To read data from Google BigQuery, configure a Google BigQuery object as the Source transformation in a mapping.
Specify the name and description of Google BigQuery source. Configure the source and advanced properties for the source object in mappings.
The following table describes the source properties that you can configure for a Google BigQuery source:
Property
Description
Connection
Name of the Google BigQuery V2 source connection. Select a source connection, or click New Parameter to define a new parameter for the source connection.
If you want to overwrite the parameter at runtime, select the Allow parameter to be overridden at run time option when you create a parameter.
Source Type
Type of the Google BigQuery source object.
Select Single Object, Multiple Objects¹, Query¹ or Parameter.
When you select single object as the source type, you can choose a table or view. For the other source object types, you can choose a table.
Object
Name of the Google BigQuery source object based on the source type selected.
Parameter
A parameter file where you define values that you want to update without having to edit the task.
Select an existing parameter for the source object or click New Parameter to define a new parameter for the source object. The Parameter property appears only if you select Parameter as the source type.
If you want to overwrite the parameter at runtime, select the Allow parameter to be overridden at run time option when you create a parameter. When the task runs, the agent uses the parameters from the file that you specify in the task advanced session properties.
Query¹
Click on Define Query and enter a valid custom query.
The Query property appears only if you select Query as the source type.
You can parameterize a custom query object at runtime in a mapping.
Select the source advanced property Use EXPORT DATA Statement to stage to use the ORDER BY clause in a custom query in staging mode.
Filter
Configure a simple filter or an advanced filter to remove rows at the source. You can improve efficiency by filtering early in the data flow.
A simple filter includes a field name, operator, and value. Use an advanced filter to define a more complex filter condition, which can include multiple conditions using the AND or OR logical operators.
Only simple filter is applicable for mappings in advanced mode.
¹ Doesn't apply to mappings in advanced mode.
The following table describes the advanced properties that you can configure for a Google BigQuery source:
Property
Description
Source Dataset ID
Optional. Overrides the Google BigQuery dataset name that you specified in the Source transformation.
Source Table Name
Optional. Overrides the Google BigQuery table name that you specified in the Source transformation.
Source Staging Dataset¹
Optional. Overrides the Google BigQuery staging dataset name that you specified in the connection and the Source Dataset ID source advanced property.
Number of Rows to Read
Specifies the number of rows to read from the Google BigQuery source table.
Allow Large Results¹
Determines whether Google BigQuery V2 Connector must produce arbitrarily large result tables to query large source tables.
If you select this option, you must specify a destination table to store the query results.
Query Results Table Name¹
Required if you select the Allow Large Results option.
Specifies the destination table name to store the query results. If the table is not present in the dataset, Google BigQuery V2 Connector creates the destination table with the name that you specify.
Job Poll Interval In Seconds¹
The number of seconds after which Google BigQuery V2 Connector polls the status of the read job operation.
Default is 10.
Read Mode
Specifies the read mode to read data from the Google BigQuery source.
You can select one the following read modes:
- Direct. In direct mode, Google BigQuery V2 Connector reads data directly from the Google BigQuery source table.
Note: When you use hybrid and complex connection mode, you cannot use direct mode to read data from the Google BigQuery source.
- Staging¹. In staging mode, Google BigQuery V2 Connector exports data from the Google BigQuery source into Google Cloud Storage. After the export is complete, Google BigQuery V2 Connector downloads the data from Google Cloud Storage into the local stage file and then reads data from the local stage file.
Default is Direct mode.
Use EXPORT DATA statement to stage
Uses the EXPORT DATA statement to export data from Google BigQuery to Google Cloud Storage.
If the query contains an ORDER BY clause, the specified order is maintained when you export the data.
This property applies to staging mode.
Number of Threads for Downloading Staging Files¹
Specifies the number of files that Google BigQuery V2 Connector downloads at a time to enable parallel download.
This property applies to staging mode.
Data format of the staging file¹
Specifies the data format of the staging file. You can select one of the following data formats:
- Avro
- JSON (Newline Delimited). Supports flat and record data with nested and repeated fields.
- CSV. Supports flat data.
Note: In a .csv file, columns of the Timestamp data type are represented as floating point numbers that cause the milliseconds value to differ.
- Parquet
This property applies to staging mode.
Local Stage File Directory¹
Specifies the directory on your local machine where Google BigQuery V2 Connector stores the Google BigQuery source data temporarily before it reads the data.
This property applies to staging mode.
Note: This property is not applicable when you use a serverless runtime environment.
Staging File Name¹
Name of the staging file where data from the Google BigQuery source table is exported to Google Cloud Storage.
This property applies to staging mode.
Enable Staging File Compression¹
Indicates whether to compress the size of the staging file in Google Cloud Storage before Google BigQuery V2 Connector reads data from the staging file.
You can enable staging file compression to reduce cost and transfer time.
This property applies to staging mode.
Persist Extract Staging File After Download¹
Indicates whether Google BigQuery V2 Connector must persist the staging file after it reads data from the staging file.
By default, Google BigQuery V2 Connector deletes the staging file.
Persist Destination Table¹
Indicates whether Google BigQuery V2 Connector must persist the query results table after it reads data from the query results table.
By default, Google BigQuery V2 Connector deletes the query results table.
pre SQL¹
SQL statement that you want to run before reading data from the source.
For example, if you want to select records in the database before you read the records from the table, specify the following pre SQL statement:
SELECT * FROM [api-project-80697026669:EMPLOYEE.DEPARTMENT] LIMIT 1000;
post SQL¹
SQL statement that you want to run after reading data from the source.
For example, if you want to update records in a table after you read the records from a source table, specify the following post SQL statement:
Overrides the default SQL query used to read data from the Google BigQuery source.
Note: When you specify SQL override query, you must specify a dataset name in the Source Dataset ID advanced source property.
Ensure that the list of selected columns, data types, and the order of the columns that appear in the query matches the columns, data types, and order in which they appear in the source object.
Ensure that you only map all the columns in the SQL override query to the target.
Does not apply when you enable partitioning.
Select the source advanced property Use EXPORT DATA Statement to stage to use the ORDER BY clause in a SQL Override Query in staging mode. When staging optimization is enabled in a mapping, the columns mapped in the SQL Override Query must match the columns in the source object.
Use Legacy SQL For SQL Override¹
Indicates that the SQL Override query is specified in legacy SQL.
Use the following format to specify a legacy SQL query for the SQL Override Query property:
SELECT <Col1, Col2, Col3> FROM [projectID:datasetID.tableName]
Clear this option to define a standard SQL override query.
Use the following format to specify a standard SQL query for the SQL Override Query property:
SELECT * FROM `projectID.datasetID.tableName`
Label¹
You can assign a label for the transformation to organize and filter the associated jobs in the Google Cloud Platform Log Explorer.
The project ID for the Google Cloud project that is linked to an active Google Cloud Billing account where the Secure Agent runs query and extract jobs.
If you omit the project ID here, the Secure Agent runs query and extract jobs in the Google Cloud project corresponding to the Project ID value specified in the Google BigQuery V2 connection.
Retry Options¹
Comma-separated list to specify the following retry options if you set Direct as the read mode:
- Retry Count. The number of retry attempts to read data from Google BigQuery.
- Retry Interval. The time in seconds to wait between each retry attempt.
- Retry Exceptions. The list of exceptions separated by pipe (|) character for which the retries are made.
Use the following format to specify the retry options:
Note: The retry options are available for preview. Preview functionality is supported for evaluation purposes but is unwarranted and is not production-ready. Informatica recommends that you use in non-production environments only. Informatica intends to include the preview functionality in an upcoming release for production use, but might choose not to in accordance with changing market or technical circumstances. For more information, contact Informatica Global Customer Support. To use the functionality, your organization must have the appropriate licenses.
Number of Spark Partitions²
Specifies the maximum number of partitions that the Spark engine splits the data into.
Default is 1.
¹ Doesn't apply to mappings in advanced mode.
² Applies only to mappings in advanced mode.
You can set the tracing level in the advanced properties session to determine the amount of details that logs contain.
The following table describes the tracing levels that you can configure:
Property
Description
Terse
The Secure Agent logs initialization information, error messages, and notification of rejected data.
Normal
The Secure Agent logs initialization and status information, errors encountered, and skipped rows due to transformation row errors. Summarizes session results, but not at the level of individual rows.
Verbose Initialization
In addition to normal tracing, the Secure Agent logs additional initialization details, names of index and data files used, and detailed transformation statistics.
Verbose Data
In addition to verbose initialization tracing, the Secure Agent logs each row that passes into the mapping. Also notes where the Secure Agent truncates string data to fit the precision of a column and provides detailed transformation statistics.
When you configure the tracing level to verbose data, the Secure Agent writes row data for all rows in a block when it processes a transformation.
Read modes
When you use Google BigQuery V2 Connector, you can read data by using direct mode or staging mode. Before you choose a mode, see the Google documentation to understand the cost implications and trade-offs for each mode.
You can read data from a Google BigQuery source by using one of the following modes:
Direct Mode
Use direct mode when the volume of data that you want to read is small. In direct mode, Google BigQuery V2 Connector directly reads data from a Google BigQuery source. You can configure the number of rows that you want Google BigQuery V2 Connector to read in one request.
Staging Mode
Use staging mode when you want to read large volumes of data in a cost-efficient manner.
In staging mode, Google BigQuery V2 Connector first exports the data from the Google BigQuery source into Google Cloud Storage. After the export is complete, Google BigQuery V2 Connector downloads the data from Google Cloud Storage into a local stage file. You can configure the local stage file directory in the advanced source properties. Google BigQuery V2 Connector then reads the data from the local stage file.
When you enable staging file compression, Google BigQuery V2 Connector compresses the size of the staging file in Google Cloud Storage. Google BigQuery V2 Connector then downloads the staging file and decompresses the staging file before it reads the file. To improve the performance and download data in parallel, you can configure the number of threads for downloading the staging file.
If a job fails, Google BigQuery V2 Connector deletes the staging file unless you configure the task or mapping to persist the staging file.
Optimize read performance in staging mode
You can configure Data Integration to create a flat file for staging when you read data from a Google BigQuery source to optimize the staging performance.
You can enhance the read operation performance by setting a staging property, INFA_DTM_RDR_STAGING_ENABLED_CONNECTORS, for the Secure Agent. Data Integration first copies the data from Google BigQuery source into a flat file located in the local staging file directory. When the staging file contains all the data, Data Integration reads the data.
You can optimize the staging performance when you read data from single or multiple Google BigQuery objects.
Enabling Google BigQuery V2 Connector to optimize the read performance
Perform the following tasks to set the staging property:
1In Administrator, click Runtime Environments. The Runtime Environments page appears.
2Select the Secure Agent for which you want to set the custom configuration property.
3Click Edit Secure Agent icon corresponding to the Secure Agent you want to edit in Actions. The Edit Secure Agent page appears.
4In the System Configuration Details section, select the Service as Data Integration Server and the type as Tomcat.
5Set the value of the Tomcat property INFA_DTM_RDR_STAGING_ENABLED_CONNECTORS to the plugin ID of the Google BigQuery V2 Connector.
You can find the plugin ID in the manifest file located in the following directory:
8In the Google BigQuery V2 connection, set the UseRFC4180CSVParser:true custom property in the Provide Optional Properties connection property.
You can check the session logs. If the flat file is created successfully, Data Integration logs the following message in the session log:
The reader is configured to run in [DTM_STAGING_CSV] mode.
In the Google BigQuery advanced source properties, set the read mode as staging and set the Data Format of the staging file property to CSV.
When you enable the staging mode to read source data, you can see the following message in the logs:
READER_1_1_1> SDKS_38636 [2022-07-26 14:59:29.056] Plug-in #601601: DTM Staging is enabled for connector for Source Instance [Source].
When you disable the staging mode to read source data, you can see the following message in the logs:
READER_1_1_1> SDKS_38637 [2022-07-26 16:46:04.312] Plug-in #601601: DTM Staging is disabled for connector for Source Instance [Source].
Rules and guidelines when you optimize the read performance
Consider the following rules when you enable the staging property:
•If you run a mapping enabled for SQL ELT optimization, the mapping does not consider the staging property and runs without staging optimization.
•When you read data of the byte data type from the Google BigQuery source, ensure that the size or precision of the binary data does not exceed 62,914,560 bytes.
•Ensure that the total size or precision of all the columns in the Google BigQuery source does not exceed 125,829,120 bytes.
•If the format of the staging file is CSV and you read from a single Google BigQuery table with multiple objects as the source type, the mapping runs without staging optimization.
•If you do not specify a valid path for the local staging file directory, the mapping fails and the session logs do not display a meaningful error message.
•When you parameterize both the Google BigQuery object type and the advanced fields, and select the Allow Parameter to be overridden at run time option while configuring the input parameters, the mapping does not consider the staging property and runs without staging optimization.
•When you configure staging optimization to process source data with the Numeric data type with a scale greater than 9 and a precision greater than 28, the mapping truncates the data to a scale of 9 while writing to the target. To preserve the same scale and precision of the Numeric data type in the target, perform the following tasks:
- Map the BigNumeric data type in the source to the Decimal data type in the target.
- Create a table in the backend database with a scale of 9.
- Specify the rounding-off mode for the data.
•When you configure staging optimization to process data and the source contains data of the Numeric or BigNumeric data types with a precision greater than 28 digits, the mapping fails with the following error:
[ERROR] Error occurred for Transformation - Source while writing the data to DTM Buffer - Data Conversion Failed
Custom query source type
You can use a custom query as a source object when you use a Google BigQuery V2 connection.
You might want to use a custom query as the source when a source object is large. You can use the custom query to reduce the number of fields that enter the data flow. You can configure a custom query to read data from one project while having the Google BigQuery V2 connection set up in another project. You can also create a parameter for the source type when you design your mapping so that you can define the query in the Mapping Task wizard.
To use a custom query as a source, select Query as the source type when you configure the source transformation and then use valid and supported SQL to define the query.
You can use legacy SQL or standard SQL to define a custom query. To define a legacy SQL custom query, you must select the Use Legacy SQL For Custom Query option when you create a Google BigQuery V2 connection. You can unselect the Use Legacy SQL For Custom Query option to define a standard SQL custom query. For more information about Google BigQuery Legacy SQL functions and operators, see Legacy SQL functions and operators.
Rules and guidelines for Google BigQuery custom queries
When you configure a custom query, consider the following guidelines:
•You cannot use custom query as a source for the following configurations:
- Key range partitions
- Data filters
- Sort
•When you specify the SESSSTARTTIME variable in a custom query to return the current date and time, use any of the following formats in the SELECT query:
- CAST('$$$SESSSTARTTIME' as TIMESTAMP(0))
- SELECT PARSE_TIMESTAMP('%m/%d/%Y %H:%M:%E6S', '$$$SESSSTARTTIME' ) as t1 --2022-10-06 18:53:28 UTC
- SELECT cast(substr(cast('$$$SESSSTARTTIME' as string),0,19) as datetime FORMAT 'MM/DD/YYYY HH24:MI:SS') as t2;
•If you provide the billing project ID in the source advanced properties, and you want to use the staging mode for the read operation and enable the Use EXPORT DATA statement to stage property, you must specify the project ID in the custom query.
•When you configure a view, you can query only to a maximum of 14 nested levels.
•When you use the query source type in a mapping to read from multiple tables, and you configure a join for one or more tables that have the same column names, the mapping fails.
For example, see the following SQL query that involves a full outer join between two tables EMPLOYEE and DEPARTMENT that are part of the SALES.PUBLIC schema, where two columns have the same name, CITY:
SELECT EMP_ID, NAME, CITY, DEPT_ID, DEPT_NAME, CITY FROM SALES.PUBLIC.EMPLOYEE FULL OUTER JOIN SALES.PUBLIC.DEPARTMENT ON EMP_ID = DEPT_ID
To distinguish the conflicting column names, add aliases that the database can refer to while joining the tables:
SELECT e.EMP_ID, e.NAME, e.CITY as ecity,d.DEPT_ID, d.DEPT_NAME, d.CITY as dcity FROM SALES.PUBLIC.EMPLOYEE e FULL OUTER JOIN SALES.PUBLIC.DEPARTMENT d ON e.EMP_ID = d.DEPT_ID
Adding multiple source objects
When you create a Source transformation, you can select Google BigQuery V2 multiple object as the source type and then configure a join to combine the tables. You can define an advanced relationship or a query to join the tables. You must use the standard SQL to define the query to join the tables.
1In the Source transformation, click the Source Type as Multiple Objects.
2From the Actions menu, click Add Source Object.
3Select the source object that you want to add from the displayed list and click OK.
4From the Related Objects Actions menu, select Advanced Relationship.
5In the Advanced Relationship window, you can click Add Object to add more objects.
6Set your own conditions or specify a query to define the relationship between the tables.
Note: When you configure a join expression, select the fields and define a join condition or a query syntax. You must prefix the Project ID before the Dataset ID in the join condition to combine multiple tables.
Use the following example to configure the join condition or join query with the Project ID prefix:
- Join condition:
`P1.D3.T3`.col5 = `P1.D2.T2`.col3 ON `P1D2.T2`.col4 = `P1.D1.T1`.col2
- Join query:
`P1.D1.T1` LEFT OUTER JOIN `P1.D2.T2` FULL OUTER JOIN `P1.D3.T3` ON `P1.D3.T3`.col5 = `P1.D2.T2`.col3 ON `P1.D2.T2`.col4 = `P1.D1.T1`.col2
In the example, P represents the Project ID, D represents the Dataset ID, and T represents the Table Name.
Note: If you configure a filter, prefix the Project ID before the Dataset ID in the filter condition. For example, provide the simple filter condition as,
`Project.Dataset.Table`.column
Note: If you specify a SQL override query for multiple source tables, you must use the following format for the SQL override query:
select `project_id.dataset.table`.col1 AL_dataset_table_col1,`project_id.dataset.table`.col2 AL_dataset_table_col2, `project_id.dataset1.table1`.col1 AL_dataset1_table1_col1,`project_id.dataset1.table1`.col2 AL_dataset1_table1_col2 from `project_id.dataset.table` <join condition> `project_id.dataset1.table1` ON `project_id.dataset.table`.col = `project_id.dataset1.table1`.col1 where <condition>
In the example, AL represents the alias prefix for the column names.
7Click OK.
The following image shows an example of an advanced join condition defined between the Google BigQuery V2 tables:
Rules and guidelines for adding multiple source objects
Consider the following rules and guidelines when you add multiple source objects:
•When you import multiple source tables, ensure that you use a Google BigQuery V2 connection in hybrid mode.
•You cannot configure partitioning when you use the advanced relationship option.
•You must not import multiple source tables with the same name from different datasets.
•You cannot use a self join when you add multiple source tables.
• When you specify a cross join condition to read data from multiple Google BigQuery tables, use the following format:
•When you configure a Filter transformation, you must not apply a filter condition with the column of Record data type in the source object.
•When you configure a Joiner or Router transformation, you must not apply a condition with the column of Record data type in the source object.
• When you configure a mapping to read data from multiple sources, ensure that the source objects does not contain columns of Record data type or Repeated columns with the same name. Otherwise, the mapping fails with the following error:
com.google.cloud.hadoop.repackaged.bigquery.com.google.api.client.googleapis.json. GoogleJsonResponseException: 400 Bad Request
•When you use special characters in column names for an advanced relationship, the query generated is not valid and the mapping task fails.
•When you click Add Object to add more objects in the Advanced Relationship window, the table might fail to load or take a long time to load. If this issue occurs, import the object again.
Partitioning
When you read data from a Google BigQuery source and use simple or hybrid connection mode, you can configure key range partitioning to optimize the mapping performance at run time.
Key range partitioning
You can configure key range partitioning in a mapping that uses the simple or hybrid connection mode to read data from Google BigQuery sources. With key range partitioning, the Secure Agent distributes rows of source data based on the fields that you define as partition keys. The Secure Agent compares the field value to the range values for each partition and sends rows to the appropriate partitions.
Use key range partitioning for columns that have an even distribution of data values. Otherwise, the partitions might have unequal size. For example, a column might have 10 rows between key values 1 and 1000 and the column might have 999 rows between key values 1001 and 2000. If the mapping includes multiple sources, use the same number of key ranges for each source.
When you define key range partitioning for a column, the Secure Agent reads the rows that are within the specified partition range. For example, if you configure two partitions for a column with the ranges as 10 through 20 and 30 through 40, the Secure Agent does not read the rows 20 through 30 because these rows are not within the specified partition range.
You can also use an in-out parameter file to specify the key range values for the partition key columns in a mapping.
You can configure a partition key for fields of the following data types:
•String
•Integer
•Numeric (only if you use a Google BigQuery connection in hybrid mode )
• Timestamp
Use the following format: YYYY-MM-DD HH24:MI:SS
Guidelines for key range partitioning in mappings
When you configure key range partitioning in a mapping, consider the following guidelines:
•You cannot configure a partition key for Record data type columns and repeated columns.
•You cannot configure key range partitioning if a row in the partition key column contains a null value.
•You cannot parameterize the value for the partition key column name in the Partition Key property.
•You cannot parameterize the key range values for a partition key column of the Timestamp data type.
•When you run a mapping with SQL ELT optimization, you cannot parameterize the key range values.
• You cannot use key range partitions when a mapping includes any of the following transformations:
- Web Services
- JSON to Relational
Configuring Key Range Partitioning
Perform the following steps to configure key range partitioning for Google BigQuery sources:
1In the Source Properties, click the Partitions tab.
2Select the required partition key from the list.
3Click Add New Key Range to define the number of partitions and the key ranges based on which the Secure Agent must partition data.
Use a blank value for the start range to indicate the minimum value. Use a blank value for the end range to indicate the maximum value.
