GoogleSQL for BigQuery supports the following federated query functions.

Function list

EXTERNAL_QUERY

EXTERNAL_QUERY('connection_id','''external_database_query'''[,'options'])

Description

Executes a query on an external database and returns the results as atemporary table. The external database data type isconverted to aGoogleSQL data type in the temporaryresult table withthese data type mappings.

• external_database_query: The query to run on the external database.

• connection_id: The ID of theconnection resource.The connection resource contains settings for the connection between theexternal database and BigQuery.If you don't have a default project configured, prepend the project ID tothe connection ID in following format:

  projects/PROJECT_ID/locations/LOCATION/connections/CONNECTION_ID

  Replace the following:

  • PROJECT_ID: The project ID.
  • LOCATION: The location of the connection.
  • CONNECTION_ID: The connection ID.

  For example,projects/example-project/locations/us/connections/sql-bq.

  Caution: If you have a view that's shared across multiple projects where you useEXTERNAL_QUERY, alwaysuse the fully qualified connection ID (projects/PROJECT_ID/locations/LOCATION/connections/CONNECTION_ID), otherwise the wrong project might be used.

• options: An optional string of a JSON format map with key value pairs ofoption name and value (both are case sensitive).

  For example:'{"default_type_for_decimal_columns":"numeric"}'

  Supported options:

  Option Name	Description
  "default_type_for_decimal_columns"	Can be "float64", "numeric", "bignumeric" or "string". With this option, the MySQL Decimal type or PostgreSQL Numeric type will be mapped to the provided BigQuery type. When this option isn't provided, the MySQL Decimal type or PostgreSQL Numeric type will be mapped to BigQuery NUMERIC type.
  "query_execution_priority"	Can be "low", "medium" or "high". Only supported in Spanner. Specifies priority for execution of the query. Execution priority is "medium" by default.

Additional notes:

• TheEXTERNAL_QUERY function is usually used in aFROM clause.
• You can use theEXTERNAL_QUERY() function to access metadataabout the external database.
• EXTERNAL_QUERY() won't honor the ordering of the external query result,even if your external query includesORDER BY.

Return Data Type

BigQuery table

Examples

Suppose you need the date of the first order for each of your customers toinclude in a report. This data isnot currently in BigQuery but is available in your operationalPostgreSQL database in . The following federated query exampleaccomplishes this and includes 3 parts:

1. Run the external querySELECT customer_id, MIN(order_date) ASfirst_order_date FROM orders GROUP BY customer_id in the operationalPostgreSQL database to get the first order date for each customer throughtheEXTERNAL_QUERY() function.
2. Join external query result table with customers table inBigQuery bycustomer_id.
3. Select customer information and first order date.

SELECTc.customer_id,c.name,SUM(t.amount)AStotal_revenue,rq.first_order_dateFROMcustomersAScINNERJOINtransaction_factAStONc.customer_id=t.customer_idLEFTOUTERJOINEXTERNAL_QUERY('connection_id','''SELECT customer_id, MIN(order_date) AS first_order_date       FROM orders       GROUP BY customer_id''')ASrqONrq.customer_id=c.customer_idGROUPBYc.customer_id,c.name,rq.first_order_date;

You can use theEXTERNAL_QUERY() function to query information_schema tablesto access database metadata, such as list all tables in the database or showtable schema. The following example information_schema queries work in bothMySQL andPostgreSQL.

-- List all tables in a database.SELECT*FROMEXTERNAL_QUERY('connection_id','''SELECT * FROM information_schema.tables''');

-- List all columns in a table.SELECT*FROMEXTERNAL_QUERY('connection_id','''SELECT * FROM information_schema.columns WHERE table_name='x';''');

EXTERNAL_QUERY() won't honor the ordering of the external query result, evenif your external query includesORDER BY. The following example query ordersrows by customer ID in the external database, but BigQuerywill not output the result rows in that order.

-- ORDER BY will not order rows.SELECT*FROMEXTERNAL_QUERY('connection_id','''SELECT * FROM customers AS c ORDER BY c.customer_id''');

Data type mappings

When you execute a federated query, the data from the external databaseare converted to GoogleSQLtypes. Below are the data type mappings fromMySQL to BigQuery andPostgreSQL to BigQuery.

Things to know about mapping:

• Most MySQL data types can be matched to the same BigQuery datatype, with a few exceptions such asdecimal,timestamp, andtime.
• PostgreSQL supports many non-standard data types which aren't supportedin BigQuery, for examplemoney,path,uuid,boxer, andothers.
• The numeric data types in MySQL and PostgreSQL will be mapped toBigQueryNUMERIC value by default. The BigQueryNUMERIC value range is smaller than in MySQL and PostgreSQL. It can also bemapped toBIGNUMERIC,FLOAT64, orSTRING with"default_type_for_decimal_columns" inEXTERNAL_QUERY options.

Error handling

If your external query contains a data type that's unsupported inBigQuery, the query will fail immediately. You can cast theunsupported data type to a different MySQL / PostgreSQL data type that issupported. Seeunsupported data typesfor more information on how to cast.

MySQL to BigQuery type mapping

PostgreSQL to BigQuery type mapping

Unsupported MySQL and PostgreSQL data types

If your external query contains a data type that's unsupported inBigQuery, the query will fail immediately. You can cast theunsupported data type to a different supported MySQL / PostgreSQL data type.

• Unsupported MySQL data type
  • Error message:Invalid table-valued function external_query Foundunsupported MySQL type in BigQuery. at [1:15]
  • Unsupported type:GEOMETRY,BIT
  • Resolution: Cast the unsupported data type to STRING.
  • Example:SELECT ST_AsText(ST_GeomFromText('POINT(1 1)'));This command casts the unsupported data typeGEOMETRY toSTRING.
• Unsupported PostgreSQL data type
  • Error message:Invalid table-valued function external_queryPostgres type (OID = 790) isn't supported now at [1:15]
  • Unsupported type:money, time with time zone, inet, path, pg_lsn,point, polygon, tsquery, tsvector, txid_snapshot, uuid, box, cidr, circle,interval, jsonb, line, lseg, macaddr, macaddr8
  • Resolution: Cast the unsupported data type to STRING.
  • Example:SELECT CAST('12.34'::float8::numeric::money AS varchar(30));This command casts the unsupported data typemoney tostring.

Spanner to BigQuery type mapping

When you execute a Spanner federated query, the data fromSpanner is converted to GoogleSQLtypes.

* PostgreSQL numeric values with a precision that's greater than the precision that BigQuery supports are rounded. Values that are larger than the maximum value generate anInvalid NUMERIC value error.

If your external query contains a data type that's unsupported for federatedqueries, the query fails immediately. You can cast the unsupported data typeto a supported data type.

SAP Datasphere to BigQuery type mapping

When you execute aSAP Datasphere federated query,the data from SAP Datasphere is converted to the following GoogleSQLtypes.