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. To publish web layers, the database connection files must be registered with the ArcGIS Enterprise portal's hosting server site. 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 Pro 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 the ArcGIS GIS Server site that acts as the ArcGIS Enterprise portal's hosting server.
Install and configure drivers
To connect from ArcGIS Pro to BigQuery, you must install the latest Magnitude Simba Open Database Connectivity (ODBC) driver on the ArcGIS Pro machines. To publish a map image layer or feature layer that contains data from BigQuery, you must install drivers and configure the machines in the ArcGIS Enterprise portal's hosting 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.
Microsoft Windows machines
Install the Magnitude Simba ODBC driver on each ArcGIS Pro machine and each machine in a hosting server that uses a Windows operating system.
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.
Linux machines
If the portal's hosting server runs on Linux machines, you must install the Magnitude Simba ODBC drivers on each machine in the GIS Server site.
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.
Alter 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.
- Ensure that the ArcGIS Server installation owner on each machine has at least read and execute permissions on the data warehouse client libraries.
- Open the init_user_param.sh script in a text editor.
- 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 - Save and close the script.
- 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 database connection files
In ArcGIS Pro, use the New Database Connection dialog box to create a connection to BigQuery.
- Open the Catalog pane in ArcGIS Pro.
- Right-click Databases or a folder under Folders and click New Database Connection.
- Choose BigQuery from the Database Platform drop-down list.
- From the Authentication Type drop-down list, choose the type of authentication to use when connecting.
- User authentication—Uses a refresh token for authentication.
- Service authentication—Uses a service account key file for authentication.
See the Google BigQuery documentation about authentication to help you decide which to use.
- If you choose User authentication, provide a refresh token.
- If you choose Service authentication, browse to the location of the key file that contains the credentials you need to connect to BigQuery, select the file, and click Open.
- Type or choose the project ID and choose a default dataset associated with the user you want to connect as.
Note:
Do not connect using any of the BigQuery public data projects as the default project. You cannot enable feature binning and cannot create a materialized view directly on any tables in the datasets of the public data projects.
If you intend to create a materialized view using this connection file, ensure the connection is assigned a default dataset in which database objects can be created. The view will be created in that dataset, which may not be in the same dataset as the data that participates in the materialized view.
- Type a name for the connection file in the Connection File Name field.
As you provide connection information, a default name is created. You can use the default, or type a more descriptive file name.
This is the name that will appear in the Catalog pane and view, and the name of the .sde file stored on disk.
- Optionally, expand the Additional Properties section and specify parameters to connect that are in addition to those required in the previous steps.
- AdditionalProjects
- Timeout
- Tracing
- LogLevel
- LogPath
If you require a property that is not available from the drop-down menu, you can type the property in the Property field and provide the appropriate value for it.
Read the Google BigQuery documentation for information about advanced connection parameters.
For example, to configure the ODBC connector to retry failed API calls for 200 seconds, choose Timeout from the Property drop-down menu, and type 200 in the Value field.
- Click OK to create the connection file.
A database connection appears under Databases in the Catalog pane, and a connection file (.sde) is created in the ArcGIS Pro project directory.
Ensure the account you used for the connection has the required privileges to access content in the data warehouse.
Register with the hosting server
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.
Next steps
After you configure the ArcGIS clients to connect and create a database connection file, you and other organization members can do the following:
- If publishers intend to create snapshots of data to be stored in the relational data store, ensure the ArcGIS Data Store administrator is aware of this and has configured sufficient memory and disk space on the relational data store machines and backup machines to accommodate it.
- Analysts and publishers create a query layer in a map in ArcGIS Pro.