Connect to Google BigQuery from ArcGIS

To connect to Google BigQuery, you must install and configure client files to implement communication between ArcGIS and BigQuery and create database connection files from ArcGIS Pro. For best performance, deploy ArcGIS clients in Google Cloud.

Typically, IT professionals install the ArcGIS software and the drivers required to connect to the data warehouse. They also configure network, firewall, and other communication channels necessary to allow communication between ArcGIS and the cloud data warehouse.

Next, the administrator of the cloud data warehouse creates accounts and grants the accounts privileges to access specific tables in the cloud data warehouse. The administrator can create a connection from ArcGIS to the cloud data warehouse and share the connection file with publishers, or the administrator can provide publishers with the credentials necessary to allow them to create their own connection files.

To publish ArcGIS Server web services that reference data in the cloud data warehouse, publishers must register the database connection file with one of the ArcGIS Server sites that are federated with ArcGIS Enterprise. To publish a web service with a snapshot, you must register the database connection file with the hosting server.

Install and configure drivers

To publish a map image layer or feature layer that contains data from BigQuery, you must install the latest Magnitude Simba Open Database Connectivity (ODBC) drivers on all machines in the federated ArcGIS Server site to which web layers will be published. You must also also configure the machines in the ArcGIS Server site to connect to BigQuery.

See the Google BigQuery documentation for more information and links to download and install the 64-bit ODBC driver.

Confirm that the path to the Magnitude Simba ODBC driver is present in the Windows PATH system environment variable. If it is not, add it.

When you edit the Magnitude Simba /etc/odbcinst.ini file to reference the location of the Simba ODBC driver, you must change the section heading to be Simba ODBC Driver for Google BigQuery. The text must match that exactly. For example, if the heading is Simba ODBC Driver for Google BigQuery 64bit or anything else, connections from ArcGIS will fail.

After you configure the /etc/odbcinst.ini file, configure the ArcGIS Server init_user_param.sh file to access the drivers.

Edit the init_user_param.sh script

Alter the init_user_param.sh script installed with ArcGIS Server to reference the drivers. You can access this script by browsing to the <ArcGIS Server installation directory>/arcgis/server/usr directory.

1. Ensure that the ArcGIS Server installation owner on each machine has at least read and execute permissions on the data warehouse client libraries.

2. Open the init_user_param.sh script in a text editor.

3. Remove the comment marks (#) from the lines beginning with export and replace the driver locations with the paths on your machine.

   # -----------------------------------------------------------------------
   # Modify this section to configure an ODBC connection to Google BigQuery Data
   # Warehouse.
   #
   # To make a connection, copy GoogleBigQueryODBC.did to the lib folder under the
   # extracted directory for Google BigQuery ODBC driver. Edit
   # simba.googlebigqueryodbc.ini and set DriverManagerEncoding=UTF-16. Configure and
   # store the odbcinst.ini in the /etc folder.
   #
   # -----------------------------------------------------------------------
   export LIB_ODBC_DRIVER_MANAGER=<Location_to_ODBC_driver_manager_libraries>/libodbc.so.2
   export SIMBAGOOGLEBIGQUERYODBCINI=<Location_to_simba.googlebigqueryodbc.ini>/simba.googlebigqueryodbc.ini
   

4. Save and close the script.

5. For the changes you made in the init_user_param.sh file to take effect, you must restart ArcGIS Server.

   To do this, run the stopserver.sh script, then run the startserver.sh script on each machine in the ArcGIS Server site.

   ./stopserver.sh

   ./startserver.sh

Create a database connection

You must have a database connection (.sde file) that authenticates the connection to the database. To use the database connection file with ArcGIS Server, you must save user information with the connection file.

You can call the ArcPy CreateDatabaseConnection function from one of the machines in the ArcGIS Server site that needs to connect to the database. See Create Database Connection in the ArcGIS Pro help for information about running this function.

Register the connection file

To allow ArcGIS Server sites to access the data, use the database connection file you created to add a registered data store in ArcGIS Pro or add a data store item in the portal.

To publish with the Create a snapshot for query layers configuration parameter enabled, you must register the database connection file with the hosting server. See the Configure parameters section of Configure a web feature layer in the ArcGIS Pro help for more information about this parameter.