BigQuery (Google)
DLH.io documentation for BigQuery (Google)
One of the most robust Cloud Data Warehouse solutions, and arguably the first, available on the market today it is a no brainer that we have Google BigQuery as a target system for DLH.io.
In order to get setup on BigQuery we use a sophisticated but elegant solution which aligns with open standards and uses the best pratices prescribed by Google, as well as a few that we have learned over hundreds of customer interactions.
Use this section of the documentation to explore:
- Basic BigQuery Setup
- BigQuery Case Senstivity
- BigQuery DLH.io Demo Video
BigQuery Case Sensitivity
Currently DLH.io abides by the case-sensitivity of BigQuery. This means that the dataset/schema and the tables are case-senstive when created and populated by DLH.io. As a future feature we will allow a force case-insensitive feature. In which case once will need to understand if their source connection has any case sensitivity which could result in an issue as the data is loaded into the target. There is potential for case-insensitivity to bcome the default standard of DLH.io
Per the BigQuery documentation on their lexical structure and syntax there are different uses of case sensitivity in the BigQuery platform.
| Category | Case-Sentitive? | Notes |
|---|---|---|
| Keywords | No | |
| Bulit-in Function Names | No | |
| UDF Names | Yes | |
| Table Names | Yes (can turn off) | Yes, for Datasets and Tables Names; unless the option "is_case_insensitive" is set to TRUE |
| Column Name | No | |
| String Values | Yes | |
| String Comparisions | Yes | |
| Aliases within a Query | No | |
| Regular Expression Matching | RegEx matching is case-sensitive by default, unless the regex expression specifies case insensitivity | |
| LIKE Matching | Yes | |
If using the is_case_insensitive option, note that it cannot be used for ALTER SCHEMA only for CREATE SCHEMA (ref: https://cloud.google.com/bigquery/docs/reference/standard-sql/data-definition-language#schema_option_list). This is set when creating the SCHEMA as follows:
Set Case Insensitivity in BigQuery
CREATE SCHEMA mydataset
OPTIONS(
is_case_insensitive=TRUE
)Setup Instructions
Follow the setup steps guide for configuring your BigQuery (Google) connection to enable data to flow into your destination via DLH.
Supported Features
Here are key features supported for this connector.
| Sync Feature | Supported | Details |
|---|---|---|
| Custom Data and Development | ✓ | Ability to enhance connector upon request |
| Historical Re-Load/Load | ✓ | |
| Incremental/Delta Load | ✓ | Gets most recent records and changes |
| Column Selection | ✓ | |
| Column Hashing | ✓ | |
| Re-Sync Table/Entity | ✓ | Select at the table level to reload data history (on next Sync Bridge run) |
| Custom Queries | Utilizing SQL Data Query Connector | |
| Custom Data | ||
| Captures Deleted Rows | ✓ | On all supported tables |
| API Sync Bridge Initiation | ✓ | |
| Priority Scheduling | ✓ | |
| Private VPC/Link | ☂ | |
| DLH Data Model Available | - |
If you have any questions about these supported features please reach out to our Customer Support team.
Details on Sync Processing
For this connector, we believe the sync processing is straightforward. We've provided a number of details, steps, and other guidance here and in the setup steps guide. Be sure to also check the change log and notes page from time to time for any changes.