BigQuery Integration and Synchronization

Google BigQuery data can be integrated and synchronized codeless with various other data sources using the Layer2 Cloud Connector via CData provider. Please take a look here for supported target systems and applications like SQL databases, Microsoft Office 365, SharePoint, Exchange, Dynamics and others.​​​​​

Querying massive datasets can be time consuming and expensive without the right hardware and infrastructure. Google BigQuery solves this problem by enabling super-fast, SQL-like queries against append-only tables, using the processing power of Google's infrastructure.

Fig.: Google BigQuery sample table to sync with local or other cloud-based data sets using the Layer2 Cloud Connector.

To connect to a Google BigQuery table the source entity must be configured as follows in the Layer2 Cloud Connector Connection Manager:

Google BigQuery Specific Integration Settings

​Please note the following Google BigQuery specific settings.

• Install the CData ADO.NET Provider for Google BigQuery 2018. This provider is not part of the Layer2 Cloud Connector ADO.NET provider package and should be installed separately from the vendor's product page. It requires extra licensing.
• Choose “CData ADO.NET Provider for Google BigQuery 2018” (exact name might vary) as “Data Provider” for your data entity (source and / or destination).
• Follow the vendor instructions for setting up the connection string and select statement (see below for samples).
• Set the parameters in the connection String accordantly to your google account. For example: InitiateOAuth=GETANDREFRESH;ProjectId=<YOUR_PROJECT_ID>;DatasetId=<YOUR_DATASET_ID>;UseLegacySQL=true;

• Write the Select Statement to get all Information. Select Statement example: SELECT commit, [author.name] as author, [committer.name] as committer, [committer.email] as committermail, repo_name,[committer.date],[author.email] as authoremail FROM `github_contributors` LIMIT 10
• Primary Key example:
• Commit,commiter.date,repo_name

• A Google client ID looks like mytoken.apps.googleusercontent.com.
• A client secret looks like one encoded string of numbers and characters.
• Projects are top-level containers in Google Cloud Platform. They store information about billing and authorized users, and they contain BigQuery data. Each project has a friendly name and a unique ID.
• Tables contain your data in BigQuery, along with a corresponding table schema that describes field names, types, and other information. BigQuery also supports views, virtual tables defined by a SQL query.
• Note: The first time new credentials are used, your default browser will open a web page where you are asked to authorize access to your Google account. Thats not the case for any further manual or automatic scheduled syncs.
• You can map your Google BigQuery data fields to specific external fields in the Layer2 Cloud Connector. Please take care about data types (simple type conversions are supported).
• No installation or changes are required at the Google BigQuery data source or data destination.
• Data synchronization can be started manually in the Connection Manager, per command line or scheduled in background by the Layer2 Cloud Connector Windows Service. Only data changes are processed (no delete / bulk import).
• No programming required for setup a connection and sync.
• No need to open your local network for access from outside.
• Two-way sync is not supported.

Known Issues and Workarounds with Google BigQuery Data Integration

• There's a known issue with Cloud Connector version 7.8+ where it cannot properly open the verification page for Google's OAuth connection. Trying to verify the connection will result in a timeout message. There are workarounds for this however. Please contact support@layer2support.com for further help.
• If the Google BigQuery dataset has no primary key, you must set one. Pick columns with a unique value or mix columns to generate a custom unique value like we did in our Primary Key mixing the values from the columns commit, committer.date and repo_nam.
• The connection direction can be bi-directional. Operations like UPDATE, INSERT, and DELETE are only supported if you use the standard SQL in your table. Set the SQL dialect to standard. You cannot specify a destination table. Let the Destination Table under settings as “No table selected”.

Step-by-Step Intros For Codeless Google BigQuery Data Integration And Synchronization

Just some ideas what to connect next:

• Sync with Microsoft Exchange (on-premises or online) for calendar events and appointments, mobile access and offline availability on any device. Public folders also supported.
• Sync with 3rd party tools like ERP/CRM/CMS based on SQL databases like Oracle, mySQL or Microsoft SQL Server, e.g. for SSRS reporting.
• Sync with Microsoft SharePoint or Office 365 for better collaboration (for example with customers or partners) or for intranet publishing. Change notofications and workflows fully supported.

Ready to go next steps?