Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Showing content from https://developers.google.com/bigquery/docs/reference/standard-sql/federated_query_functions below:

Federated query functions | BigQuery

Stay organized with collections Save and categorize content based on your preferences.

GoogleSQL for BigQuery supports the following federated query functions.

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

Description

Executes a query on an external database and returns the results as a temporary table. The external database data type is converted to a GoogleSQL data type in the temporary result table with these data type mappings.

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

• connection_id: The ID of the connection resource. The connection resource contains settings for the connection between the external database and BigQuery. If you don't have a default project configured, prepend the project ID to the 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 use EXTERNAL_QUERY, always use 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 of option 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:

• The EXTERNAL_QUERY function is usually used in a FROM clause.
• You can use the EXTERNAL_QUERY() function to access metadata about the external database.
• EXTERNAL_QUERY() won't honor the ordering of the external query result, even if your external query includes ORDER BY.

Return Data Type

BigQuery table

Examples

Suppose you need the date of the first order for each of your customers to include in a report. This data is not currently in BigQuery but is available in your operational PostgreSQL database in . The following federated query example accomplishes this and includes 3 parts:

1. Run the external query SELECT customer_id, MIN(order_date) AS first_order_date FROM orders GROUP BY customer_id in the operational PostgreSQL database to get the first order date for each customer through the EXTERNAL_QUERY() function.
2. Join external query result table with customers table in BigQuery by customer_id.
3. Select customer information and first order date.

SELECT
  c.customer_id, c.name, SUM(t.amount) AS total_revenue, rq.first_order_date
FROM customers AS c
INNER JOIN transaction_fact AS t ON c.customer_id = t.customer_id
LEFT OUTER JOIN
  EXTERNAL_QUERY(
    'connection_id',
    '''SELECT customer_id, MIN(order_date) AS first_order_date
       FROM orders
       GROUP BY customer_id'''
  ) AS rq
  ON rq.customer_id = c.customer_id
GROUP BY c.customer_id, c.name, rq.first_order_date;

You can use the EXTERNAL_QUERY() function to query information_schema tables to access database metadata, such as list all tables in the database or show table schema. The following example information_schema queries work in both MySQL and PostgreSQL.

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

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

EXTERNAL_QUERY() won't honor the ordering of the external query result, even if your external query includes ORDER BY. The following example query orders rows by customer ID in the external database, but BigQuery will not output the result rows in that order.

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

When you execute a federated query, the data from the external database are converted to GoogleSQL types. Below are the data type mappings from MySQL to BigQuery and PostgreSQL to BigQuery.

Things to know about mapping:

• Most MySQL data types can be matched to the same BigQuery data type, with a few exceptions such as decimal, timestamp, and time.
• PostgreSQL supports many non-standard data types which aren't supported in BigQuery, for example money, path, uuid, boxer, and others.
• The numeric data types in MySQL and PostgreSQL will be mapped to BigQuery NUMERIC value by default. The BigQuery NUMERIC value range is smaller than in MySQL and PostgreSQL. It can also be mapped to BIGNUMERIC, FLOAT64, or STRING with "default_type_for_decimal_columns" in EXTERNAL_QUERY options.

Error handling

If your external query contains a data type that's unsupported in BigQuery, the query will fail immediately. You can cast the unsupported data type to a different MySQL / PostgreSQL data type that is supported. See unsupported data types for more information on how to cast.

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

• Unsupported MySQL data type
  • Error message: Invalid table-valued function external_query Found unsupported 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 type GEOMETRY to STRING.
• Unsupported PostgreSQL data type
  • Error message: Invalid table-valued function external_query Postgres 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 type money to string.

When you execute a Spanner federated query, the data from Spanner is converted to GoogleSQL types.

* 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 an Invalid NUMERIC value error.

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

When you execute a SAP Datasphere federated query, the data from SAP Datasphere is converted to the following GoogleSQL types.

Scale (s) defines the number of digits after the decimal point and can be between 0 and p.

Except as otherwise noted, the content of this page is licensed under the Creative Commons Attribution 4.0 License, and code samples are licensed under the Apache 2.0 License. For details, see the Google Developers Site Policies. Java is a registered trademark of Oracle and/or its affiliates.

Last updated 2025-08-07 UTC.

[[["Easy to understand","easyToUnderstand","thumb-up"],["Solved my problem","solvedMyProblem","thumb-up"],["Other","otherUp","thumb-up"]],[["Hard to understand","hardToUnderstand","thumb-down"],["Incorrect information or sample code","incorrectInformationOrSampleCode","thumb-down"],["Missing the information/samples I need","missingTheInformationSamplesINeed","thumb-down"],["Other","otherDown","thumb-down"]],["Last updated 2025-08-07 UTC."],[[["`EXTERNAL_QUERY` executes a query on an external database, returning the results as a temporary BigQuery table, with external data types mapped to GoogleSQL types."],["The `EXTERNAL_QUERY` function is commonly used in a `FROM` clause and can access external database metadata; however, it does not preserve the external query's `ORDER BY` clause."],["Connection to an external database is done using a `connection_id`, which is the resource ID containing settings for the connection, and can include optional parameters in JSON format for fine-tuning data type mappings."],["Data type mappings are defined between external database types (MySQL, PostgreSQL, Spanner, SAP Datasphere) and GoogleSQL, and in case of unsupported types, data can be casted to compatible types to avoid query failures."],["The default mapping for MySQL and PostgreSQL numeric data types is `NUMERIC`, but can also be set to `BIGNUMERIC`, `FLOAT64`, or `STRING` by using the `default_type_for_decimal_columns` option, while Spanner numerics with too much precision are rounded off, and values that are too large generate errors."]]],[]]

RetroSearch is an open source project built by @garambo | Open a GitHub Issue

Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo

HTML: 3.2 | Encoding: UTF-8 | Version: 0.7.4