Google BigQuery Source Component
The Google BigQuery Source Component is an SSIS data flow pipeline component that can be used to read/retrieve data from Google BigQuery.
The component includes the following two pages to configure how you want to read data from Google BigQuery.
- General
- Columns
General Page
The General page of Google BigQuery Source Component allows you to specify the general settings of the component.
- Connection Manager
-
The Google BigQuery Source Component requires a connection in order to connect to Google BigQuery. The Connection Manager drop-down will show a list of all connection managers that are available to your current SSIS packages.
- Object Type
-
The Object Type drop-down contains a list of available Report Types in Google BigQuery.
- PROJECT: Lists all projects to which you have been granted any project role
- DATASET: Lists all datasets in the specified project to which you have been granted the Dataset - READER role
- JOB: Lists all jobs that you started in the specified project
- TABLE: Lists all tables in the specified dataset to which you have been granted the Dataset - READER role
- TABLEDATA: Retrieves table data from the specified table
- QUERY: Run a BigQuery SQL query and returns results if the query completes
- Billing Project(since v22.1)
-
The Billing Project option allows you to specify which billing project is for executing jobs.
Note: This option is only available when the Object Type is Query.
- Project
-
The Project option allows you to specify which project you want to read from Google BigQuery. The drop-down will present a list of all available projects in your Google BigQuery.
- Dataset
-
The Dataset option allows you to specify which dataset you want to read from Google BigQuery. The drop-down will present a list of all available datasets in the specified project.
- Create Query Job
-
The Create Query Job option allows you to create a query job in Big Query when executing BigQuery SQL query text.
Note: This option is only available when working with QUERY Object Type.
- Poll Throttle Rate
-
The Poll Throttle Rate option will limit the number of polling requests that can be sent per second. This is useful to limit the rates to get Query Job status when you enable the Create Query Job option.
Note: This option is only available when Create Query Job option is enabled.
- Page Size
-
The Page Size option allows you to specify how many records you want to retrieve each time.
- Output As JSON
-
The Output As JSON option specifies whether the output should be one single output column that contains the values in JSON format for each row returned by Google BigQuery.
Note: This option is only available when the Object Type is either TABLE DATA or Query.
- Use Query Cache
-
The Use Query Cache option allows you to specify whether to look for the result in the query cache. This option is unchecked by default.
- Use Legacy SQL
-
The Use Legacy SQL option allows you to specify whether to use Google BigQuery's legacy SQL dialect for this query. This option is checked by default. If you uncheck this option, the query will use Google BigQuery's standard SQL.
- Query
-
The Query text box lets you specify a snippet of a query to retrieve data or create a query job in Google BigQuery.
Since v21.1, we support opening views so that you can use the toolbar to open a BigQuery table view to get the query.
- Table
-
The Table option allows you to specify which table you want to read from Google BigQuery. The drop-down will present a list of all available tables in the specified dataset.
- Job Projection
-
The Job Projection option allows you to restrict information returned to a set of selected fields. There are two options available:
- Full: Includes all job data
- Minimal: Does not include the job configuration
Note: This option is only available when the Object Type JOB is selected.
- Job State
-
The Job State option allows you to filter jobs by their running state. There are four options available:
- All: returns jobs in all states
- Done: returns finished jobs
- Pending: returns pending jobs
- Running: returns running jobs
Note: This option is only available when the Object Type JOB is selected.
- Include All Users
-
The Include All Users option allows you to specify whether to display jobs owned by all users in the project.
Note: This option is only available when the Object Type JOB is selected and requires you to have the Project - Is Owner role for the specified project.
- Start Row
-
The Start Row option allows you to specify the index of the starting row to read. The default value is 0.
Note: Row index in Google BigQuery is zero-based. When you set the Start Row option to 0, the component will read the TABLEDATA from first row. This option is only available when the Object Type TABLEDATA is selected.
- Refresh Component Button
-
Clicking the Refresh Component button causes the component to retrieve the latest metadata and update each field to its most recent metadata.
- Expression fx Button
-
Click the fx button to launch SSIS Expression Editor to enable dynamic update of the property at run time.
- Generate Documentation Button
-
Click the Generate Documentation button to generate a Word document which describes the component's metadata including relevant mapping and more.
Columns Page
The Columns page of the Google BigQuery Source Component shows you all available fields from the report type that you specified on the General page.
On the top left of the grid, the checkbox can be used to toggle the selection of all available fields. This is a productive way to check or uncheck all available fields.
- The Columns Page grid consists of:
-
- Google BigQuery Field: Column that will be retrieved from Google BigQuery.
- Data Type: The data type of this field.
- Properties window for the field listed
- Name: Specify the column name.
- Data type: The data type can be changed according.
- Length: Specify the Length of the fields. If the data type specified is a string, the length specified here would be the maximum size. If the data type is not a string, the length will be ignored.
- Precision: Specify the number of digits in a number.
- Scale: Specify the number of digits to the right of the decimal point in a number.
- CodePage: Specify the Code Page of the field.