...
Now CDAP provides the interface for users to handle their datasets in BigQuery.
Use-case
Users want to integrate CDAP with their already stored dataset in Google BigQuery.
User Stories
1. As a Useruser, I would like to run arbitrary queries synchronously queries synchronously against my datasets in BigQuery and pull those records into in a hydrator pipeline.
2.
Requirements
- User should
...
3. User is able to specify the limit size of the dataset to query.
4. User is able to poll for the result.
5. User can list the query result history for a duration of time.
6. The schema is automatically pulled from the table.
7. User can pull the field names from the query.
Example
...
- provide the correct project id which he has access to.
- User should provide the SQL query against a dataset inside his project.
- User should provide the Json Key File of the project.
- User should provide the temporary google cloud storage bucket to store the intermediate query result.
- User should provide the fully qualified input table in BigQuery. If import query is not provided, this table will be downloaded to
temporary Google Cloud Storage directory and finally to the CDAP source. If import query is given, this table will be a intermediate
storage to cache the query result and then downloaded to the temporary directory in Google Cloud Storage before going to CDAP source. - The temporary Google Cloud Storage directory should be deleted manually to avoid unnecessary charge from Google.
Example
Following are two simple examples showing how BigQuery Source would work.
A dataset already exist in Google BigQuery:121
...
project Id:
...
vernal-project1
dataset name: baby_names
table name: names_2014
name | count |
---|---|
Emma | 100 |
Oscar | 334 |
Peter | 223 |
Jay | 1123 |
Nicolas | 764 |
example1:
Code Block |
---|
...
|
Design
CDAP provides two type of operations on the dataset stored in BigQuery: Query and Poll Results.
Users can use Query operation to do SQL query on specified dataset in BigQuery.
For Poll Results, user can fetch the result using specified job ID or fetch the a specified number of latest query results.
Query:
Inputs | type | required | default |
---|---|---|---|
ProjectId | String | Yes |
|
Credencial | String | Yes | |
Query | String | Yes | |
Limit Time | Integer (min) | No | 10 |
Limit Size | Integer (GB) | No | 50 |
Poll Results:
Using jobId:
Inputs | type | Required |
---|---|---|
PorjectId | String | Yes |
JobId | String | Yes |
Polling Latest Results:
...
| |
{
"name": "BigQuery",
"properties": {
"referenceName": "bigquery",
"projectId": "vernal-project1",
"tempBuketPath": "gs://bucketName.datasetName/tableName",
"jsonFilePath": "/path/to/jsonkeyfile",
"InputTableId": "vernal-project1:babynames.names_2014",
"outputSchema": "name:string,count:int"
}
}
}
|
This source will read the vernal-project1:babynames.names_2014 table, download the whole table to gs://bucketName.datasetName/tableName, and then get the data from there.
example2:
Code Block | ||
---|---|---|
| ||
{
"name": "BigQuery",
"properties": {
"referenceName": "bigquery",
"projectId": "vernal-project1",
"tempBuketPath": "gs://bucketName.datasetName/tableName",
"jsonFilePath": "/path/to/jsonkeyfile",
"importQuery":"SELECT name as babyName, count as nameCount FROM [vernal-project1:babynames.names_2014] ORDER BY count DESC LIMIT 3",
"InputTableId": "vernal-project1:babynames.blankTable",
"outputSchema": "babyName:string,babyCount:int"
}
}
}
|
Before running this source, user should create a blank table with schema : {babyname:string, babyCount:int}. File in r example, make this blank table in vernal-project1:babynames.
and the output of the source is as follows:
babyName | babyCount |
---|---|
Jay | 1123 |
Nicolas | 764 |
Oscar | 334 |