Google Big Query JDBC driver returns an incorrect primary keys information while Google Big Query does not actually define primary keys

Jun 2, 2022
Knowledge

Title

URL Name

Google-Big-Query-JDBC-driver-returns-an-incorrect-primary-keys-information-while-Google-Big-Query-does-not-actually-define-primary-keys

Article Number

000215540

Environment

Product: Connect for JDBC Google Big Query driver Version: All supported versions OS: All supported platforms Database: Google Big Query Application: All supported applications

Question/Problem Description

Google Big Query JDBC driver returns an incorrect primary keys information while Google Big Query does not actually define primary keys

Steps to Reproduce

1. Create table t1 in BigQuery - Create table t1(col1 int, col2 bool); 2. Fetch primary keys and indexes 3. Using a sample Java codes and find that it can fetch primary keys: - conn.getMetaData().getPrimaryKeys(null, "schema.dataset", "t1"); The primary keys and indexes are not set in table t1 indeed (BigQuery doesn't support primary key and index indeed) Observed results: -------------------------- Connected to 6 0 GoogleBigQuery 6.0.0.000865 (C3110.F001064.U000500) PKs.... pk:col1, key seq:1 -----------------------------

Clarifying Information

Since Google Big Query has no keys, to simulate a primary key it just always marks the first column in each table as the primary key.

DataDirect has introduced a new connection option that the user can use. To enable this in the event the application requires each table have a primary key.

Google BigQuery does not have the concept of 'primary key' or even 'uniqueness.' However, some applications will not function properly without at least one column in each table being designated the primary key. This option allows the user to specify which column should take that designation.

Error Message

Defect Number

Defect XDBC-34592

Enhancement Number

Cause

Resolution

Fixed in hot fix 6.0.0.001321 (C4595.F001549.U000777)
Refer to "Connect for JDBC hot fix download and install instructions” for instructions on how to download and install the hot fix.

New Connection Property Name: PrimaryKeyPattern

Purpose: Determines which column in a table is designated as the primary key. Google BigQuery does not have the concept of primary keys, or even uniqueness. However, some applications will not function properly without at least one column in a table designated as the primary key. This property allows your applications that require a primary key to function correctly when connecting to Google BigQuery data sources.

Valid Values: * | <column_name>

where:
<column_name> is the name of the column, specified as a regular expression, that you want designated as the primary key in each table.

Behavior:
If set to *, the driver designates the first column in each table that is not of the BOOL, RECORD, ARRAY or GEOGRAPHY data type as the primary key.

If set to <column_name>, the driver designates the first column in each table whose name matches the specified regular expression. The driver will not designate any column that is of the BOOL, RECORD, ARRAY, or GEOGRAPHY data type as the primary key.

If no value is specified, the driver does not designate a primary key for any tables. This is the typical behavior for Google BigQuery data sources.

Workaround

Notes

Keyword Phrase

Last Modified Date

6/2/2022 12:07 PM

Google Big Query JDBC driver returns an incorrect primary keys information while Google Big Query does not actually define primary keys

Files(0)

Was this article helpful?

Was this article helpful?

Google Big Query JDBC driver returns an incorrect primary keys information while Google Big Query does not actually define primary keys

Information

Related Articles