[](https://travis-ci.com/anelendata/tap-bigquery)
# tap-bigquery
Extract data from BigQuery tables.
This is a [Singer](https://singer.io) tap that produces JSON-formatted data
following the [Singer spec](https://github.com/singer-io/getting-started/blob/master/SPEC.md).
This tap:
- Pulls data from Google BigQuery tables/views with datetime field.
- Infers the schema for each resource and produce catalog file.
- Incrementally pulls data based on the input state.
## Installation
### Step 0: Acknowledge LICENSE and TERMS
Please especially note that the author(s) of tap-bigquery is not responsible
for the cost, including but not limited to BigQuery cost) incurred by running
this program.
### Step 1: Activate the Google BigQuery API
(originally found in the [Google API docs](https://googlecloudplatform.github.io/google-cloud-python/latest/bigquery/usage.html))
1. Use [this wizard](https://console.developers.google.com/start/api?id=bigquery-json.googleapis.com) to create or select a project in the Google Developers Console and activate the BigQuery API. Click Continue, then Go to credentials.
2. On the **Add credentials to your project** page, click the **Cancel** button.
3. At the top of the page, select the **OAuth consent screen** tab. Select an **Email address**, enter a **Product name** if not already set, and click the **Save** button.
4. Select the **Credentials** tab, click the **Create credentials** button and select **OAuth client ID**.
5. Select the application type **Other**, enter the name "Singer BigQuery Tap", and click the **Create** button.
6. Click **OK** to dismiss the resulting dialog.
7. Click the Download button to the right of the client ID.
8. Move this file to your working directory and rename it *client_secrets.json*.
Export the location of the secret file:
```
export GOOGLE_APPLICATION_CREDENTIALS="./client_secret.json"
```
For other authentication method, please see Authentication section.
### Step 2: Install
First, make sure Python 3 is installed on your system or follow these
installation instructions for Mac or Ubuntu.
```
pip install -U tap-bigquery
```
Or you can install the lastest development version from GitHub:
```
pip install --no-cache-dir https://github.com/anelendata/tap-bigquery/archive/master.tar.gz#egg=tap-bigquery
```
## Run
### Step 1: Configure
Create a file called tap_config.json in your working directory, following
config.sample.json:
```
{
"streams": [
{"name": "<some_schema_name>",
"table": "`<project>.<dataset>.<table>`",
"columns": ["<col_name_0>", "<col_name_1>", "<col_name_2>"],
"datetime_key": "<your_key>",
"filters": ["country='us'", "state='CA'",
"registered_on>=DATE_ADD(current_date, INTERVAL -7 day)"
] // also optional: these are parsed in 'WHERE' clause
}
],
"start_datetime": "2017-01-01T00:00:00Z", // This can be set at the command line argument
"end_datetime": "2017-02-01T00:00:00Z", // end_datetime is optional
"limit": 100,
"start_always_inclusive": false // default is false, optional
}
```
- The required parameters is at least one stream (one bigquery table/view) to copy.
- It is not a recommended BigQuery practice to use `*` to specify the columns
as it may blow up the cost for a table with a large number of columns.
- `filters` are optional but we strongly recommend using this over a large
partitioned table to control the cost. LIMIT (The authors of tap-bigquery is not
responsible for the cost incurred by running this program. Always test
thoroughly with small data set first.)
- `start_datetime` must also be set in the config file or as the command line
argument (See the next step).
- `limit` will limit the number of results, but it does not result in reduce
the query cost.
The table/view is expected to have a column to indicate the creation or
update date and time so the tap sends the query with `ORDER BY` and use
the column to record the bookmark (See State section).
### Step 2: Create catalog
Run tap-bigquery in discovery mode to let it create json schema file and then
run them together, piping the output of tap-bigquery to target-csv:
```
tap-bigquery -c tap_config.json -d > catalog.json
```
### Step 3: Run
tap-bigquery can be run with any Singer Target. As example, let use
[target-csv](https://github.com/singer-io/target-csv).
```
pip install target-csv
```
Run:
```
tap-bigquery -c tap_config.json \
--catalog catalog.json --start_datetime '2020-08-01T00:00:00Z' \
--end_datetime '2020-08-02T01:00:00Z' | target-csv --config target_config.json \
> state.json
```
This example should create a csv file in the same directory.
`state.json` should contain a state (bookmark) after the run. (See State section).
Notes:
- start and end datetimes accept ISO 8601 format, can be date only. start datetime
is inclusive, end datetime is not.
- It is recommended to inspect the catalog file and fix the auto-type assignment
if necessary.
- target-csv's target_config.json is optinal.
- tap-bigquery can produce nested records but it's up to target if the data
writing will be successful. In this example with target-csv, the table is
expected to be flat.
## Authentication
It is recommended to use `tap-bigquery` with a service account.
- Download the client_secrets.json file for your service account, and place it
on the machine where `tap-bigquery` will be executed.
- Set a `GOOGLE_APPLICATION_CREDENTIALS` environment variable on the machine,
where the value is the fully qualified path to client_secrets.json
In environments where it is preferable to supply secrets or other configuration via
environment variables or where it is not feasible to make a `client_secrets.json`
file available, you may instead provide credential as a JSON string in the
`GOOGLE_APPLICATION_CREDENTIALS_STRING` environment variable. This JSON string
should contain the same contents and be formatted the same way as the contents
of a `client_secrets.json` file.
In the testing environment, you can also manually authenticate before runnig
the tap. In this case you do not need `GOOGLE_APPLICATION_CREDENTIALS` defined:
```
gcloud auth application-default login
```
You may also have to set the project:
```
gcloud config set project <project-id>
```
Though not tested, it should also be possible to use the OAuth flow to
authenticate to GCP as well:
- `tap-bigquery` will attempt to open a new window or tab in your default
browser. If this fails, copy the URL from the console and manually open it
in your browser.
- If you are not already logged into your Google account, you will be prompted
to log in.
- If you are logged into multiple Google accounts, you will be asked to select
one account to use for the authorization.
- Click the **Accept** button to allow `tap-bigquery` to access your Google BigQuery
table.
- You can close the tab after the signup flow is complete.
## State
This tap emits [state](https://github.com/singer-io/getting-started/blob/master/docs/CONFIG_AND_STATE.md#state-file).
The command also takes a state file input with `--state <file-name>` option.
If the state is set, start_datetime config and command line argument are
ignored and the datetime value from last_update key is used as the resuming
point.
To avoid the data duplication, start datetime is exclusive
`start_datetime < datetime_column` when the tap runs with state option. If
you fear a data loss because of this, just use the `--start_datetime` option
instead of state. Or set `start_always_inclusive: true` in configuration.
The tap itself does not output a state file. It anticipate the target program
or a downstream process to fianlize the state safetly and produce a state file.
## Original repo
https://github.com/anelendata/tap-bigquery
# About this project
This project is developed by
ANELEN and friends. Please check out the ANELEN's
[open innovation philosophy and other projects](https://anelen.co/open-source.html)
---
Copyright © 2020~ Anelen Co., LLC
Raw data
{
"_id": null,
"home_page": "https://github.com/anelendata/tap_bigquery",
"name": "tap-bigquery",
"maintainer": "",
"docs_url": null,
"requires_python": "",
"maintainer_email": "",
"keywords": "",
"author": "Daigo Tanaka, Anelen Co., LLC",
"author_email": "",
"download_url": "https://files.pythonhosted.org/packages/f8/bb/7e46f8a4ff3ab5f9f6f019d9e55134245acdf52ec1d88b2746d5897573c8/tap-bigquery-0.3.7.tar.gz",
"platform": null,
"description": "[](https://travis-ci.com/anelendata/tap-bigquery)\n\n# tap-bigquery\n\nExtract data from BigQuery tables.\n\nThis is a [Singer](https://singer.io) tap that produces JSON-formatted data\nfollowing the [Singer spec](https://github.com/singer-io/getting-started/blob/master/SPEC.md).\n\nThis tap:\n\n- Pulls data from Google BigQuery tables/views with datetime field.\n- Infers the schema for each resource and produce catalog file.\n- Incrementally pulls data based on the input state.\n\n## Installation\n\n### Step 0: Acknowledge LICENSE and TERMS\n\nPlease especially note that the author(s) of tap-bigquery is not responsible\nfor the cost, including but not limited to BigQuery cost) incurred by running\nthis program.\n\n### Step 1: Activate the Google BigQuery API\n\n (originally found in the [Google API docs](https://googlecloudplatform.github.io/google-cloud-python/latest/bigquery/usage.html))\n\n 1. Use [this wizard](https://console.developers.google.com/start/api?id=bigquery-json.googleapis.com) to create or select a project in the Google Developers Console and activate the BigQuery API. Click Continue, then Go to credentials.\n 2. On the **Add credentials to your project** page, click the **Cancel** button.\n 3. At the top of the page, select the **OAuth consent screen** tab. Select an **Email address**, enter a **Product name** if not already set, and click the **Save** button.\n 4. Select the **Credentials** tab, click the **Create credentials** button and select **OAuth client ID**.\n 5. Select the application type **Other**, enter the name \"Singer BigQuery Tap\", and click the **Create** button.\n 6. Click **OK** to dismiss the resulting dialog.\n 7. Click the Download button to the right of the client ID.\n 8. Move this file to your working directory and rename it *client_secrets.json*.\n\n\nExport the location of the secret file:\n\n```\nexport GOOGLE_APPLICATION_CREDENTIALS=\"./client_secret.json\"\n```\n\nFor other authentication method, please see Authentication section.\n\n### Step 2: Install\n\nFirst, make sure Python 3 is installed on your system or follow these\ninstallation instructions for Mac or Ubuntu.\n\n```\npip install -U tap-bigquery\n```\n\nOr you can install the lastest development version from GitHub:\n\n```\npip install --no-cache-dir https://github.com/anelendata/tap-bigquery/archive/master.tar.gz#egg=tap-bigquery\n```\n\n## Run\n\n### Step 1: Configure\n\nCreate a file called tap_config.json in your working directory, following\nconfig.sample.json:\n\n```\n{\n \"streams\": [\n {\"name\": \"<some_schema_name>\",\n \"table\": \"`<project>.<dataset>.<table>`\",\n \"columns\": [\"<col_name_0>\", \"<col_name_1>\", \"<col_name_2>\"],\n \"datetime_key\": \"<your_key>\",\n \"filters\": [\"country='us'\", \"state='CA'\",\n \"registered_on>=DATE_ADD(current_date, INTERVAL -7 day)\"\n ] // also optional: these are parsed in 'WHERE' clause\n }\n ],\n \"start_datetime\": \"2017-01-01T00:00:00Z\", // This can be set at the command line argument\n \"end_datetime\": \"2017-02-01T00:00:00Z\", // end_datetime is optional\n \"limit\": 100,\n \"start_always_inclusive\": false // default is false, optional\n}\n```\n\n- The required parameters is at least one stream (one bigquery table/view) to copy.\n - It is not a recommended BigQuery practice to use `*` to specify the columns\n as it may blow up the cost for a table with a large number of columns.\n - `filters` are optional but we strongly recommend using this over a large\n partitioned table to control the cost. LIMIT (The authors of tap-bigquery is not\n responsible for the cost incurred by running this program. Always test\n thoroughly with small data set first.)\n- `start_datetime` must also be set in the config file or as the command line\n argument (See the next step).\n- `limit` will limit the number of results, but it does not result in reduce\n the query cost.\n\nThe table/view is expected to have a column to indicate the creation or\nupdate date and time so the tap sends the query with `ORDER BY` and use\nthe column to record the bookmark (See State section).\n\n### Step 2: Create catalog\n\nRun tap-bigquery in discovery mode to let it create json schema file and then\nrun them together, piping the output of tap-bigquery to target-csv:\n\n```\ntap-bigquery -c tap_config.json -d > catalog.json\n```\n\n### Step 3: Run\n\ntap-bigquery can be run with any Singer Target. As example, let use\n[target-csv](https://github.com/singer-io/target-csv).\n\n```\npip install target-csv\n```\n\nRun:\n\n```\ntap-bigquery -c tap_config.json \\\n --catalog catalog.json --start_datetime '2020-08-01T00:00:00Z' \\\n --end_datetime '2020-08-02T01:00:00Z' | target-csv --config target_config.json \\\n > state.json\n```\n\nThis example should create a csv file in the same directory.\n`state.json` should contain a state (bookmark) after the run. (See State section).\n\nNotes:\n\n- start and end datetimes accept ISO 8601 format, can be date only. start datetime\n is inclusive, end datetime is not.\n- It is recommended to inspect the catalog file and fix the auto-type assignment\n if necessary.\n- target-csv's target_config.json is optinal.\n- tap-bigquery can produce nested records but it's up to target if the data\n writing will be successful. In this example with target-csv, the table is\n expected to be flat.\n\n## Authentication\n\nIt is recommended to use `tap-bigquery` with a service account.\n\n- Download the client_secrets.json file for your service account, and place it\n on the machine where `tap-bigquery` will be executed.\n- Set a `GOOGLE_APPLICATION_CREDENTIALS` environment variable on the machine,\n where the value is the fully qualified path to client_secrets.json\n\nIn environments where it is preferable to supply secrets or other configuration via\nenvironment variables or where it is not feasible to make a `client_secrets.json`\nfile available, you may instead provide credential as a JSON string in the\n`GOOGLE_APPLICATION_CREDENTIALS_STRING` environment variable. This JSON string\nshould contain the same contents and be formatted the same way as the contents\nof a `client_secrets.json` file.\n\nIn the testing environment, you can also manually authenticate before runnig\nthe tap. In this case you do not need `GOOGLE_APPLICATION_CREDENTIALS` defined:\n\n```\ngcloud auth application-default login\n```\n\nYou may also have to set the project:\n\n```\ngcloud config set project <project-id>\n```\n\nThough not tested, it should also be possible to use the OAuth flow to\nauthenticate to GCP as well:\n- `tap-bigquery` will attempt to open a new window or tab in your default\n browser. If this fails, copy the URL from the console and manually open it\n in your browser.\n- If you are not already logged into your Google account, you will be prompted\n to log in.\n- If you are logged into multiple Google accounts, you will be asked to select\n one account to use for the authorization.\n- Click the **Accept** button to allow `tap-bigquery` to access your Google BigQuery\n table.\n- You can close the tab after the signup flow is complete.\n\n## State\n\nThis tap emits [state](https://github.com/singer-io/getting-started/blob/master/docs/CONFIG_AND_STATE.md#state-file).\nThe command also takes a state file input with `--state <file-name>` option.\nIf the state is set, start_datetime config and command line argument are\nignored and the datetime value from last_update key is used as the resuming\npoint.\n\nTo avoid the data duplication, start datetime is exclusive\n`start_datetime < datetime_column` when the tap runs with state option. If\nyou fear a data loss because of this, just use the `--start_datetime` option\ninstead of state. Or set `start_always_inclusive: true` in configuration.\n\nThe tap itself does not output a state file. It anticipate the target program\nor a downstream process to fianlize the state safetly and produce a state file.\n\n## Original repo\nhttps://github.com/anelendata/tap-bigquery\n\n# About this project\n\nThis project is developed by\nANELEN and friends. Please check out the ANELEN's\n[open innovation philosophy and other projects](https://anelen.co/open-source.html)\n\n\n---\n\nCopyright © 2020~ Anelen Co., LLC\n",
"bugtrack_url": null,
"license": "",
"summary": "Singer.io tap for extracting data from BigQuery",
"version": "0.3.7",
"split_keywords": [],
"urls": [
{
"comment_text": "",
"digests": {
"blake2b_256": "71176b976f2f2b60380bf052fb81295e2a7fbbf213c8b17654f606e809851ceb",
"md5": "dc8fa4f95cddd86840b1a13c6974083d",
"sha256": "ad452d7855a782e60b1888e5fc85af23933deb4fa3c627a265872ab8d5970db1"
},
"downloads": -1,
"filename": "tap_bigquery-0.3.7-py3-none-any.whl",
"has_sig": false,
"md5_digest": "dc8fa4f95cddd86840b1a13c6974083d",
"packagetype": "bdist_wheel",
"python_version": "py3",
"requires_python": null,
"size": 14691,
"upload_time": "2023-04-16T15:43:54",
"upload_time_iso_8601": "2023-04-16T15:43:54.106177Z",
"url": "https://files.pythonhosted.org/packages/71/17/6b976f2f2b60380bf052fb81295e2a7fbbf213c8b17654f606e809851ceb/tap_bigquery-0.3.7-py3-none-any.whl",
"yanked": false,
"yanked_reason": null
},
{
"comment_text": "",
"digests": {
"blake2b_256": "f8bb7e46f8a4ff3ab5f9f6f019d9e55134245acdf52ec1d88b2746d5897573c8",
"md5": "93cacc648a66d12a9cdd4db0da1f25b8",
"sha256": "687bebdcceb8bd64fa7da94252c7110a9e3059958c14ad490264a206bc050cf7"
},
"downloads": -1,
"filename": "tap-bigquery-0.3.7.tar.gz",
"has_sig": false,
"md5_digest": "93cacc648a66d12a9cdd4db0da1f25b8",
"packagetype": "sdist",
"python_version": "source",
"requires_python": null,
"size": 13676,
"upload_time": "2023-04-16T15:43:55",
"upload_time_iso_8601": "2023-04-16T15:43:55.420809Z",
"url": "https://files.pythonhosted.org/packages/f8/bb/7e46f8a4ff3ab5f9f6f019d9e55134245acdf52ec1d88b2746d5897573c8/tap-bigquery-0.3.7.tar.gz",
"yanked": false,
"yanked_reason": null
}
],
"upload_time": "2023-04-16 15:43:55",
"github": true,
"gitlab": false,
"bitbucket": false,
"github_user": "anelendata",
"github_project": "tap_bigquery",
"travis_ci": true,
"coveralls": false,
"github_actions": false,
"lcname": "tap-bigquery"
}
JSON
