The Easysoft ODBC-Google BigQuery Driver is installed on the computer where your applications are running. ODBC applications access ODBC drivers through the ODBC Driver Manager and a data source. The data source tells the Driver Manager which ODBC driver to load and contains Google BigQuery connection details. This chapter describes how to create data sources and configure the Easysoft ODBC-Google BigQuery Driver.
Before setting up a data source, you must have successfully installed the Easysoft ODBC-Google BigQuery Driver.
For Easysoft ODBC-Google BigQuery Driver installation instructions, see Installation.
This section describes how to configure the Easysoft ODBC-Google BigQuery Driver to connect to BiqQuery by using a data source. The section assumes you are, or are able to consult with, a database administrator.
There are two ways to set up a data source to your Google BigQuery data:
By default, the Easysoft ODBC-Google BigQuery Driver installation creates a SYSTEM data source named [BQ_SAMPLE]. If you are using the unixODBC included in the Easysoft ODBC-Google BigQuery Driver distribution, the SYSTEM odbc.ini file is in /etc.
If you built unixODBC yourself, or installed it from some other source, SYSTEM data sources are stored in the path specified with the configure option --sysconfdir=directory. If sysconfdir was not specified when unixODBC was configured and built, it defaults to /usr/local/etc.
If you accepted the default choices when installing the Easysoft ODBC-Google BigQuery Driver, USER data sources must be created and edited in $HOME/.odbc.ini.
|
To display the directory where unixODBC stores SYSTEM and USER data sources, type odbcinst -j. By default, you must be logged in as root to edit a SYSTEM data source defined in /etc/odbc.ini. |
You can either edit the sample data source or create new data sources.
Each section of the odbc.ini file starts with a data source name in square brackets [ ] followed by a number of attribute=value pairs.
The Driver attribute identifies the ODBC driver in the odbcinst.ini file to use for a data source.
When the Easysoft ODBC-Google BigQuery Driver is installed into unixODBC, it places an Easysoft BigQuery entry in odbcinst.ini. For Easysoft ODBC-Google BigQuery Driver data sources therefore, you need to include a Driver = Easysoft Big Query ODBC driver entry.
To configure a Google BigQuery data source, in your odbc.ini file, you need to specify:
Description=Easysoft Big Query ODBC driver
The Easysoft ODBC-Google BigQuery Driver must be able to find the following shared objects, which are installed during the Easysoft ODBC-Google BigQuery Driver installation:
By default, this is located in /usr/local/easysoft/unixODBC/lib.
By default, this is located in /usr/local/easysoft/lib.
By default, this is located in /usr/local/easysoft/lib.
You may need to set and export LD_LIBRARY_PATH, SHLIB_PATH or LIBPATH (depending on your operating system and run-time linker) to include the directories where libodbcinst.so, libeslicshr.so and libessupp.so are located.
|
The shared object file extension (.so) may vary depending on the operating system (.so, .a or .sl). |
The isql query tool lets you test your Easysoft ODBC-Google BigQuery Driver data sources.
To test the Easysoft ODBC-Google BigQuery Driver connection
1. Change directory into /usr/local/easysoft/unixODBC/bin.
2. Type ./isql.sh -v data_source, where data_source is the name of the target data source.
3. At the prompt, type an SQL query. For example:
SQL> SELECT * FROM `bigquery-public-data.hud_zipcode_crosswalk.census_tracts_to_zipcode` LIMIT 1
Type help to return a list of tables:
To connect an ODBC application on a Windows machine to a Google BigQuery:
1. Open ODBC Data Source Administrator:
The ODBC Data Source Administrator dialog box is displayed:
2. Select the User DSN tab to set up a data source that only you can access.
Select the System DSN tab to create a data source which is available to anyone who logs on to this Windows machine.
3. Click Add... to add a new data source.
The Create New Data Source dialog box displays a list of drivers:
4. Select Easysoft ODBC-Google BigQuery Driver and click Finish.
The DSN Setup dialog box is displayed.
For details of the attributes that can be set on this dialog box, see Attribute Fields.
To use the Easysoft ODBC-Google BigQuery Driver to work with your BigQuery data, you need either to obtain a refresh token or create a Service Account in the Google console.
To obtain a refresh token on Windows, choose the Authenticate and obtain token button in the configuration dialog box when setting up your data source. This launches your default browser. Sign into your Google account and then allow the Easysoft ODBC-Google BigQuery Driver to View and manage your data in Google BigQuery when prompted.Then follow the onscreen instructions.
To obtain a refresh token on Linux and UNIX, change to the installation_directory/easysoft/bigquery and then run bq_oauth. In a web browser on the same machine, go to the URL returned by the program. Sign into your Google account and then allow the Easysoft ODBC-Google BigQuery Driver to View and manage your data in Google BigQuery when prompted. Then follow the onscreen instructions. Paste the refresh token returned by this process into your ODBC data source in odbc.ini. The refresh token is the value for the Refresh_Token attribute.
You can also grant access to the Easysoft ODBC-Google BigQuery Driver to your BigQuery data by setting up a Google Service Account in the Google console. The Google Service Account member needs to be a BigQuery Admin. You need to generate a JSON format key for the account. Download the key to your Easysoft ODBC-Google BigQuery Driver machine. Specify the path to your key file in the Service Acct File attribute (Windows ODBC data source) or SAKeyFile attribute (Linux / UNIX ODBC data source).
This section lists the attributes which can be set for the Easysoft ODBC-Google BigQuery Driver in a table showing:
Attributes which are text fields are displayed as value.
Attributes which are logical fields can contain either 0 (to set to off) or 1 (to set to on) and are displayed as "0|1".
If an attribute can contain one of several specific values then each possible entry is displayed and separated by a pipe symbol.
For example, in the statement:
the value entered may be "1", "2" or "3".
The name of the User or System data source to be created, as used by the application when calling the SQLConnect or SQLDriverConnect functions.
Descriptive text that may be retrieved by certain applications to describe the data source.
The refresh token that you obtain from Google for authorising access to BigQuery. This is an alternative authentication mechanism to using a Service Account.
If you want to use the Service Account Authentication mechanism, set this option to the full path of the .json key file that is used to authenticate the service account email address. The key file is generated when setting up Service Account Account Authentication in the Google console
The BigQuery project ID. If you do not specify a project ID the Easysoft ODBC-Google BigQuery Driver will attempt to discover the during the connection process. The method used depends on whether you have chosen to use User authentication or Service Account authentication.
This is the project that will be used if you specify an unqualified table name in a SQL query. For example, select * from MyTable as opposed to SELECT * FROM `bigquery-public-data.utility_eu.time`.
The BigQuery dataset. If you do not specify a dataset, the Easysoft ODBC-Google BigQuery Driver will attempt to discover the data set during the connection process.
This is the dataset that will be used if you specify an unqualified table name in a SQL query.
A comma-separated list of public BigQuery projects that the Easysoft ODBC-Google BigQuery Driver can access and use as catalogs.
The length that the Easysoft ODBC-Google BigQuery Driver reports for string columns. If you are using the driver under Oracle, and get the error "illegal use of long data type", try setting this attribute to 8000. If you are using the driver under SQL Server and get the error "requested conversion is not supported" try setting this attribute to 2048.
Enable this attribute if you want to use positional parameters in your SQL statements.
If you want to use legacy BigQuery SQL syntax, enable this attribute.
Whether the Easysoft ODBC-Google BigQuery Driver reports a BigNumeric column as a string. Enable this attribute if you want to work with BigNumeric columns in SQL Server.
Whether to return fractional seconds from a TIMESTAMP column. For example, select MyTimeStampCol from MyTestTable returns 2021-04-07 10:38:55 (HP Timestamp OFF) or 2021-04-07 10:38:34.986427 (HP Timestamp ON).
If you enable this option, a timestamp ODBC escape sequence is mapped to a datetime. For example, when this option is enabled, ts = {ts '2001-02-03 04:05:06'} becomes ts = DATETIME "2001-02-03 04:05:06".
When enabled the Easysoft ODBC-Google BigQuery Driver, reports TIME columns as a timestamp.
© 1993 - 2024 Easysoft Limited. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other trademarks and registered trademarks appearing on easysoft.com are the property of their respective owners.
