標準 SQL への移行

BigQuery がサポートする SQL 言語には、標準 SQLレガシー SQL の 2 つがあります。このトピックでは、構文関数セマンティクスを含め、この 2 つの SQL 言語の違いを説明し、標準 SQL の特長をいくつかの例で示します。

レガシー SQL と標準 SQL の比較

これまで、BigQuery は BigQuery SQL という非標準 SQL 言語を使用してクエリを実行していました。BigQuery 2.0 から、BigQuery は標準 SQL のサポートをリリースし、BigQuery SQL はレガシー SQL と改名されています。BigQuery に保存されたデータのクエリを実行するには、標準 SQL の使用が推奨されます。

標準 SQL に移行する必要があるか

レガシー SQL から標準 SQL への移行は推奨されますが、必須ではありません。たとえば、レガシー SQL を使用する多くのクエリを実行しているが、新しいクエリには標準 SQL の機能を利用したいとします。レガシー SQL を使用したクエリと一緒に実行する標準 SQL を使用した新しいクエリを作成できます。

標準 SQL を有効にする

BigQuery からクエリを実行する場合、レガシー SQL と標準 SQL のどちらを使用するか選択できます。BigQuery UI、CLI、API、またはどのインタフェースを使用する場合でも、標準 SQL を有効にする手順については、標準 SQL を有効にするをご覧ください。

標準 SQL の利点

標準 SQL は SQL 2011 標準に準拠し、ネストされ繰り返されたデータのクエリをサポートする拡張機能を備えています。次のようなレガシー SQL に勝るいくつかの利点があります。

これらの機能の一部を示す例については、標準 SQL の要点をご覧ください。

型の違い

レガシー SQL 型と標準 SQL には同等の型がありますが、型の名前が異なる場合があります。次の表はレガシー SQL の各データ型と標準 SQL のその同等の型を一覧にしています。

レガシー SQL 標準 SQL
BOOL BOOL
INTEGER INT64
FLOAT FLOAT64
STRING STRING
BYTES BYTES
RECORD STRUCT
REPEATED ARRAY
TIMESTAMP TIMESTAMP TIMESTAMP の違いをご覧ください
DATE DATE レガシー SQL では DATE のサポートに制限があります
TIME TIME レガシー SQL では TIME のサポートに制限があります
DATETIME DATETIME レガシー SQL では DATETIME のサポートに制限があります

標準 SQL 型システムの詳細については、標準 SQL データ型リファレンスをご覧ください。BigQuery のデータ型の詳細については、BigQuery データ型リファレンスをご覧ください。

TIMESTAMP の違い

標準 SQL では、レガシー SQL よりも、有効な TIMESTAMP 値の範囲が制限されています。標準 SQL の有効な TIMESTAMP 値は 0001-01-01 00:00:00.0000009999-12-31 23:59:59.999999 です。たとえば、標準 SQL を使用して TIMESTAMP の最小値と最大値を選択するには次のようにします。

#standardSQL
SELECT
  min_timestamp,
  max_timestamp,
  UNIX_MICROS(min_timestamp) AS min_unix_micros,
  UNIX_MICROS(max_timestamp) AS max_unix_micros
FROM (
  SELECT
    TIMESTAMP '0001-01-01 00:00:00.000000' AS min_timestamp,
    TIMESTAMP '9999-12-31 23:59:59.999999' AS max_timestamp
);

このクエリは、min_unix_micros として -62135596800000000max_unix_micros として 253402300799999999 を返します。

選択した列に含まれるタイムスタンプ値がこの範囲でない場合は、エラーを受け取ります。

#standardSQL
SELECT timestamp_column_with_invalid_values
FROM MyTableWithInvalidTimestamps;

このクエリは次のエラーを返します。

Cannot return an invalid timestamp value of -8446744073709551617
microseconds relative to the Unix epoch. The range of valid
timestamp values is [0001-01-1 00:00:00, 9999-12-31 23:59:59.999999]

このエラーを修正する 1 つの方法としては、ユーザー定義関数を定義して、無効なタイムスタンプをフィルタリングするために使用します。

#standardSQL
CREATE TEMP FUNCTION TimestampIsValid(t TIMESTAMP) AS (
  t >= TIMESTAMP('0001-01-01 00:00:00') AND
  t <= TIMESTAMP('9999-12-31 23:59:59.999999')
);

SELECT timestamp_column_with_invalid_values
FROM MyTableWithInvalidTimestamps
WHERE TimestampIsValid(timestamp_column_with_invalid_values);

エラーを修正するもう 1 つの方法は、タイムスタンプ列を指定して SAFE_CAST 関数を使用することです。次に例を示します。

#standardSQL
SELECT SAFE_CAST(timestamp_column_with_invalid_values AS STRING) AS timestamp_string
FROM MyTableWithInvalidTimestamps;

このクエリは、無効なタイムスタンプ値の場合にタイムスタンプ文字列ではなく NULL を返します。

構文の違い

予約済みキーワードと無効な識別子のエスケープ

レガシー SQL では、予約済みキーワードと、スペース()やハイフン(-)などの無効な文字が含まれる識別子を角括弧 [] を使用してエスケープします。標準 SQL では、キーワードと識別子をバッククォート ` を使用してエスケープします。次に例を示します。

#standardSQL
SELECT
  word,
  SUM(word_count) AS word_count
FROM
  `bigquery-public-data.samples.shakespeare`
WHERE word IN ('me', 'I', 'you')
GROUP BY word;

レガシー SQL では予約済みキーワードを使用できる場所でも、標準 SQL では使用できないことがあります。たとえば、次のクエリは標準 SQL を使用すると Syntax error のために失敗します。

#standardSQL
SELECT
  COUNT(*) AS rows
FROM
  `bigquery-public-data.samples.shakespeare`;

このエラーを修正するには、バッククォートを使用してエイリアス rows をエスケープします。

#standardSQL
SELECT
  COUNT(*) AS `rows`
FROM
  `bigquery-public-data.samples.shakespeare`;

予約済みキーワードと、有効な識別子となるものの一覧については、語彙の構造をご覧ください。

プロジェクト修飾テーブル名

レガシー SQL では、プロジェクト修飾名でテーブルをクエリするには、区切り文字としてコロン(:)を使用します。次に例を示します。

#legacySQL
SELECT
  word
FROM
  [bigquery-public-data:samples.shakespeare]
LIMIT 1;

標準 SQL では、代わりにピリオド(.)を使用します。次に例を示します。

#standardSQL
SELECT
  word
FROM
  `bigquery-public-data.samples.shakespeare`
LIMIT 1;

プロジェクト名に example.com:myproject などのドメインが含まれている場合、プロジェクト名として「:」を含めて、example.com:myproject を使用します。

テーブル デコレータとワイルドカード関数

標準 SQL はTABLE_DATE_RANGETABLE_DATE_RANGE_STRICTTABLE_QUERY 関数をサポートしていません。

_TABLE_SUFFIX 疑似列にフィルタを使用して、TABLE_DATE_RANGETABLE_QUERY の同じセマンティクスを実現できます。たとえば、次のレガシー SQL クエリについて考えてみます。これは、アメリカ海洋大気庁の GSOD(Global Summary of the Day)表の 2010 年から 2011 年にかけての行数をカウントします。

#legacySQL
SELECT COUNT(*)
FROM TABLE_QUERY([bigquery-public-data:noaa_gsod],
                 'table_id IN ("gsod2010", "gsod2011")');

標準 SQL を使用した同等のクエリは次のようになります。

#standardSQL
SELECT COUNT(*)
FROM `bigquery-public-data.noaa_gsod.*`
WHERE _TABLE_SUFFIX IN ("gsod2010", "gsod2011");

TABLE_DATE_RANGE の使用例を含め、詳細についてはレガシー SQL のワイルドカード関数からの移行をご覧ください。

SELECT リスト内の末尾のカンマ

レガシー SQL と異なり、標準 SQL では、FROM 句の前の末尾のカンマは使用できません。例えば、次のクエリは無効です。

#standardSQL
SELECT
  word,
  corpus,  -- Error due to trailing comma
FROM
  `bigquery-public-data.samples.shakespeare`
LIMIT 1;

エラーを修正するには、corpus の後のカンマを削除します。

#standardSQL
SELECT
  word,
  corpus
FROM
  `bigquery-public-data.samples.shakespeare`
LIMIT 1;

カンマ演算子とテーブル

レガシー SQL で、カンマ演算子 , はテーブルに適用した場合に、UNION ALL の非標準の意味を持ちます。標準 SQL では、カンマ演算子は JOIN の標準の意味を持ちます。たとえば、次のレガシー SQL クエリについて考えてみます。

#legacySQL
SELECT
  x,
  y
FROM
  (SELECT 1 AS x, "foo" AS y),
  (SELECT 2 AS x, "bar" AS y);

これは次の標準 SQL クエリと同等です。

#standardSQL
SELECT
  x,
  y
FROM
  (SELECT 1 AS x, "foo" AS y UNION ALL
   SELECT 2 AS x, "bar" AS y);

標準 SQL では、UNION ALL は名前ではなく位置によって列を関連付けます。上記のクエリは次と同等です。

#standardSQL
SELECT
  x,
  y
FROM
  (SELECT 1 AS x, "foo" AS y UNION ALL
   SELECT 2, "bar");

標準 SQL のカンマ演算子の一般的な使用方法は、配列で JOIN を行うことです。次に例を示します。

#standardSQL
WITH T AS (
  SELECT 0 AS x, [1, 2, 3] AS arr UNION ALL
  SELECT 1, [4, 5])
SELECT
  x,
  y
FROM
  T,
  UNNEST(arr) AS y;

これは arr の要素で、テーブル T の外積を返します。このクエリは標準 SQL で次のように表現することもできます。

#standardSQL
WITH T AS (
  SELECT 0 AS x, [1, 2, 3] AS arr UNION ALL
  SELECT 1, [4, 5])
SELECT
  x,
  y
FROM
  T
JOIN
  UNNEST(arr) AS y;

このクエリでは、JOIN は、その上の例での TUNNEST(arr) AS y を区切る , カンマ演算子と同じ意味を持ちます。

論理ビュー

言語間の構文とセマンティクスの違いのため、レガシー SQL で定義された論理ビューを標準 SQL を使用して照会することはできず、その逆も同じです。代わりに標準 SQL を使用する新しいビューを(別の名前で)作成し、レガシー SQL を使用するビューを交換する必要があります。

例として、ビュー V がレガシー SQL を使用して、次のように定義されているとします。

#legacySQL
SELECT *, UTC_USEC_TO_DAY(timestamp_col) AS day
FROM MyTable;

ビュー W がレガシー SQL を使用して次のように定義されているとします。

#legacySQL
SELECT user, action, day
FROM V;

次のレガシー SQL クエリを日常的に実行していますが、代わりに標準 SQL を使用するように移行したいとします。

#legacySQL
SELECT EXACT_COUNT_DISTINCT(user), action, day
FROM W
GROUP BY action, day;

移行パスの一例は、別の名前を使用して新しいビューを作成することです。手順は次のとおりです。

次のコンテンツで、標準 SQL を使用して V2 という名前のビューを作成します。

#standardSQL
SELET *, EXTRACT(DAY FROM timestamp_col) AS day
FROM MyTable;

次のコンテンツで、標準 SQL を使用して W2 という名前のビューを作成します。

#standardSQL
SELECT user, action, day
FROM V2;

日常的に実行するクエリを標準 SQL を使用して W2 を参照するように変更します。

#standardSQL
SELECT COUNT(DISTINCT user), action, day
FROM W2
GROUP BY action, day;

もう 1 つのオプションは、ビュー VW を削除してから、同じ名前で標準 SQL を使用して再作成することです。ただし、このオプションでは V または W を参照するすべてのクエリを同時に標準 SQL を使用するように移行する必要があります。

関数の比較

次に、レガシー SQL 関数とそれらと同等の標準 SQL 関数の一部のリストを示します。

レガシー SQL 標準 SQL 備考
INTEGER(x) SAFE_CAST(x AS INT64)
CAST(x AS INTEGER) SAFE_CAST(x AS INT64)
DATEDIFF(t1, t2) TIMESTAMP_DIFF(t1, t2, DAY)
NOW CURRENT_TIMESTAMP
STRFTIME_UTC_USEC(t, fmt) FORMAT_TIMESTAMP(fmt, t)
UTC_USEC_TO_DAY(t) TIMESTAMP_TRUNC(t, DAY)
REGEXP_MATCH(s, pattern) REGEXP_CONTAINS(s, pattern)
IS_NULL(x) x IS NULL
LEFT(s, len) SUBSTR(s, 0, len)
RIGHT(s, len) SUBSTR(s, -len)
s CONTAINS "foo" STRPOS(s, "foo") > 0 または s LIKE '%foo%'
x % y MOD(x, y)
NEST(x) ARRAY_AGG(x)
ANY(x) ANY_VALUE(x)
GROUP_CONCAT_UNQUOTED(s, sep) STRING_AGG(s, sep)
SOME(x) IFNULL(LOGICAL_OR(x), false)
EVERY(x) IFNULL(LOGICAL_AND(x), true)
COUNT(DISTINCT x) APPROX_COUNT_DISTINCT(x) この後の説明を参照
EXACT_COUNT_DISTINCT(x) COUNT(DISTINCT x) この後の説明を参照
QUANTILES(x, buckets + 1) APPROX_QUANTILES(x, buckets)
TOP(x, num), COUNT(*) APPROX_TOP_COUNT(x, num)
NTH(index, arr) WITHIN RECORD arr[SAFE_ORDINAL(index)]
COUNT(arr) WITHIN RECORD ARRAY_LENGTH(arr)
HOST(url) NET.HOST(url) 後で説明する違いを参照
TLD(url) NET.PUBLIC_SUFFIX(url) 後で説明する違いを参照
DOMAIN(url) NET.REG_DOMAIN(url) 後で説明する違いを参照
PARSE_IP(addr_string) NET.IPV4_TO_INT64(NET.IP_FROM_STRING(addr_string))
FORMAT_IP(addr_int64) NET.IP_TO_STRING(NET.IPV4_FROM_INT64(addr_int64 & 0xFFFFFFFF))
PARSE_PACKED_IP(addr_string) NET.IP_FROM_STRING(addr_string)
FORMAT_PACKED_IP(addr_bytes) NET.IP_TO_STRING(addr_bytes)

標準 SQL 関数の詳細については、関数と演算子のトピックをご覧ください。

COUNT 関数の比較

レガシー SQL と標準 SQL のどちらにも COUNT 関数は含まれています。ただし、使用する SQL 言語に応じて、それぞれの関数の動作は異なります。

レガシー SQL では COUNT(DISTINCT x) は概算の数が返されます。標準 SQL では正確な数が返されます。個別値のおよその数を求める場合は、APPROX_COUNT_DISTINCT を使用すると、実行時間が短く、必要なリソースも少なくてすみます。

URL 関数の比較

レガシー SQL と標準 SQL のどちらにも、URI を解析する関数は含まれています。レガシー SQL では、HOST(url)TLD(url)DOMAIN(url) です。標準 SQL では、NET.HOST(url)NET.PUBLIC_SUFFIX(url)NET.REG_DOMAIN(url) です。

レガシー SQL 関数に対する改善点

  • 標準 SQL の URL 関数は "//" で始まる URL を解析できます。
  • 入力が RFC 3986 に準拠しない場合、または URL(たとえば "mailto:?to=&subject=&body=")ではない場合、入力の解析に別のルールが適用されます。特に、標準 SQL の URL 関数は、"//" がない非標準入力("www.google.com" など)を解析できます。最適な結果を得るには、入力が RFC 3986 に準拠する URL であることを確認してください。
  • NET.PUBLIC_SUFFIX は、先頭にピリオドを付けずに結果を返します。つまり、".com" ではなく "com" を返します。これは、公開サフィックス リストの形式に準拠しています。
  • NET.PUBLIC_SUFFIXNET.REG_DOMAIN は、大文字と国際化ドメイン名をサポートしています。これらは TLDDOMAIN ではサポートされません(予期されない結果が返される場合があります)。

エッジケースに関する細かな違い

  • 公開サフィックス リストのサフィックスが入力に含まれない場合、NET.PUBLIC_SUFFIXNET.REG_DOMAIN は NULL を返しますが、TLDDOMAIN はベスト エフォートとして推定した非 NULL 値を返します。
  • 先頭のラベル("http://com" など)がない公開サフィックスのみが入力に含まれる場合、NET.PUBLIC_SUFFIX は公開サフィックスを返しますが、TLD は空の文字列を返します。同様のケースで、NET.REG_DOMAIN は NULL を返しますが、DOMAIN は公開サフィックスを返します。
  • IPv6 ホストを含む入力の場合、NET.HOSTRFC 3986 の仕様に沿って、結果からブラケットを除去しません。
  • IPv4 ホストを含む入力の場合、NET.REG_DOMAIN は NULL を返しますが、DOMAIN は最初の 3 つのオクテットを返します。

次の表のグレーの文字は、レガシー SQL と標準 SQL の結果が同じであることを示します。

URL(説明) HOST NET.HOST TLD NET.PUBLIC _SUFFIX DOMAIN NET.REG_DOMAIN
"//google.com"
("//" で始まる)
NULL "google.com" NULL "com" NULL "google.com"
"google.com"
(非標準、"//" なし)
NULL "google.com" NULL "com" NULL "google.com"
"http://user:pass@word@x.com"
(非標準、複数の "@")
"word@x.com" "x.com" ".com" "com" "word@x.com" "x.com"
"http://foo.com:1:2"
(非標準、複数の ":")
"foo.com:1" "foo.com" ".com:1" "com" "foo.com" "foo.com"
"http://x.Co.uk"
(大文字)
"x.Co.uk" "x.Co.uk" ".uk" "Co.uk" "Co.uk" "x.Co.uk"
"http://a.b"
(公開サフィックスなし)
"a.b" "a.b" ".b" NULL "a.b" NULL
"http://com"
(ホストに公開サフィックスのみが含まれる)
"com" "com" "" "com" "com" NULL
"http://[::1]"
(IPv6 ホスト、公開サフィックスではない)
"::1" "[::1]" "" NULL "::1" NULL
"http://1.2.3.4"
(IPv4 ホスト、公開サフィックスではない)
"1.2.3.4" "1.2.3.4" "" NULL "1.2.3" NULL

繰り返しフィールドの処理の違い

レガシー SQL の REPEATED 型は、標準 SQL でのその型の ARRAY と同等です。たとえば、REPEATED INTEGER は、標準 SQL では ARRAY<INT64> に相当します。次のセクションで、レガシー SQL と標準 SQL での繰り返しフィールドのオペレーションの違いをいくつか説明します。

NULL 要素と NULL 配列

標準 SQL は NULL 配列要素をサポートしていますが、クエリ結果に NULL 配列要素がある場合、エラーが発生します。クエリ結果に NULL 配列の列がある場合、標準 SQL では空の配列として保存されます。

ネストされて繰り返されたリーフ フィールドの選択

レガシー SQL を使用すると、繰り返しが発生する場所を考慮する必要がなく、ネストされて繰り返されるフィールドに「ドット」を挿入できます。標準 SQL では、ネストされ繰り返されたフィールドに「ドット」を挿入しようとすると、エラーになります。次に例を示します。

#standardSQL
SELECT
  repository.url,
  payload.pages.page_name
FROM
  `bigquery-public-data.samples.github_nested`
LIMIT 5;

このクエリを実行すると、次が返されます。

Cannot access field page_name on a value with type
ARRAY<STRUCT<action STRING, html_url STRING, page_name STRING, ...>>

エラーを修正し、結果で page_name の配列を返すには、代わりに ARRAY サブクエリを使用します。次に例を示します。

#standardSQL
SELECT
  repository.url,
  ARRAY(SELECT page_name FROM UNNEST(payload.pages)) AS page_names
FROM
  `bigquery-public-data.samples.github_nested`
LIMIT 5;

配列と ARRAY サブクエリの詳細については、配列の操作のトピックをご覧ください。

繰り返しフィールドのフィルタリング

レガシー SQL で、WHERE 句を使用して、繰り返しフィールドを直接フィルタできます。標準 SQL では、JOIN カンマ演算子の後にフィルタを指定して、同様のロジックを表現できます。たとえば、次のレガシー SQL クエリについて考えてみます。

#legacySQL
SELECT
  payload.pages.title
FROM
  [bigquery-public-data:samples.github_nested]
WHERE payload.pages.page_name IN ('db_jobskill', 'Profession');

このクエリは page_namedb_jobskill または Profession であるページのすべての title を返します。標準 SQL で同様のクエリを次のように表現できます。

#standardSQL
SELECT
  page.title
FROM
  `bigquery-public-data.samples.github_nested`,
  UNNEST(payload.pages) AS page
WHERE page.page_name IN ('db_jobskill', 'Profession');

前述のレガシー SQL クエリと標準 SQL クエリの違いとして、[Flatten Results] オプションを設定解除してレガシー SQL クエリを実行した場合、payload.pages.title はクエリ結果で REPEATED になります。標準 SQL で同じセマンティクスを実現し、title 列の配列を返すには、代わりに ARRAY サブクエリを使用します。

#standardSQL
SELECT
  title
FROM (
  SELECT
    ARRAY(SELECT title FROM UNNEST(payload.pages)
          WHERE page_name IN ('db_jobskill', 'Profession')) AS title
  FROM
    `bigquery-public-data.samples.github_nested`)
WHERE ARRAY_LENGTH(title) > 0;

このクエリは、title の配列を作成します。ここで page_name は「'db_jobskill'」または「'Profession'」のいずれかです。次に ARRAY_LENGTH(title) > 0 を使用して、配列がその条件に一致しなかった行をフィルタします。

配列の詳細については、配列の操作のトピックをご覧ください。

選択されてネストされたリーフ フィールドの構造

[Flatten Results] オプションを設定解除すると、レガシー SQL ではネストされたリーフ フィールドの構造が SELECT リストで維持されますが、標準 SQL では維持されません。たとえば、次のレガシー SQL クエリについて考えてみます。

#legacySQL
SELECT
  repository.url,
  repository.has_downloads
FROM
  [bigquery-public-data.samples.github_nested]
LIMIT 5;

[Flatten Results] が設定解除されている場合、このクエリは repository というレコード内に urlhas_downloads を返します。次の標準 SQL クエリについて考えてみます。

#standardSQL
SELECT
  repository.url,
  repository.has_downloads
FROM
  `bigquery-public-data.samples.github_nested`
LIMIT 5;

このクエリはトップレベル列として、url および has_downloads を返します。それらは repository レコードや構造体に含まれません。それらを構造体の一部として返すには、STRUCT 演算子を使用します。

#standardSQL
SELECT
  STRUCT(
    repository.url,
    repository.has_downloads) AS repository
FROM
  `bigquery-public-data.samples.github_nested`
LIMIT 5;

FLATTEN による繰り返しの削除

標準 SQL にはレガシー SQL のように FLATTEN 関数がありませんが、JOIN(カンマ)演算子を使用して類似のセマンティクスを実現できます。たとえば、次のレガシー SQL クエリについて考えてみます。

#legacySQL
SELECT
  repository.url,
  payload.pages.page_name
FROM
  FLATTEN([bigquery-public-data:samples.github_nested], payload.pages.page_name)
LIMIT 5;

標準 SQL で同様のクエリを次のように表現できます。

#standardSQL
SELECT
  repository.url,
  page.page_name
FROM
  `bigquery-public-data.samples.github_nested`,
  UNNEST(payload.pages) AS page
LIMIT 5;

または以下のように、カンマ , 演算子の代わりに、JOIN を使用します。

#standardSQL
SELECT
  repository.url,
  page.page_name
FROM
  `bigquery-public-data.samples.github_nested`
JOIN
  UNNEST(payload.pages) AS page
LIMIT 5;

1 つの重要な違いは、レガシー SQL クエリは、payload.pages.page_nameNULL で、payload.pages が空の場合に行を返すことです。標準 SQL クエリは、payload.pages が空の場合に行を返しません。厳密に同じセマンティクスを実現するには、LEFT JOIN または LEFT OUTER JOIN を使用します。例:

#standardSQL
SELECT
  repository.url,
  page.page_name
FROM
  `bigquery-public-data.samples.github_nested`
LEFT JOIN
  UNNEST(payload.pages) AS page
LIMIT 5;

配列の詳細については、配列の操作のトピックをご覧ください。UNNEST の詳細については、UNNEST のトピックをご覧ください。

OMIT RECORD IF による行のフィルタリング

レガシー SQL の OMIT IF 句を使用して、繰り返しフィールドに適用可能な条件に基づいて、行をフィルタリングできます。標準 SQL では、EXISTS 句、IN 句、または同様のフィルタによって、OMIT IF 句をモデル化できます。たとえば、次のレガシー SQL クエリについて考えてみます。

#legacySQL
SELECT
  repository.url,
FROM
  [bigquery-public-data:samples.github_nested]
OMIT RECORD IF
  EVERY(payload.pages.page_name != 'db_jobskill'
        AND payload.pages.page_name != 'Profession');

類似の標準 SQL クエリは次のようになります。

#standardSQL
SELECT
  repository.url
FROM
  `bigquery-public-data.samples.github_nested`
WHERE EXISTS (
  SELECT 1 FROM UNNEST(payload.pages)
  WHERE page_name = 'db_jobskill'
    OR page_name = 'Profession');

ここで、ページ名が 'db_jobskill' または 'Profession' の場合に、payload.pages の要素が 1 つ以上ある場合、EXISTS 句は true に評価されます。

または、レガシー SQL クエリで IN を使用しているとします。

#legacySQL
SELECT
  repository.url,
FROM
  [bigquery-public-data:samples.github_nested]
OMIT RECORD IF NOT
  SOME(payload.pages.page_name IN ('db_jobskill', 'Profession'));

標準 SQL では、EXISTS 句を IN で使用してクエリを表すことができます。

#standardSQL
SELECT
  repository.url
FROM
  `bigquery-public-data.samples.github_nested`
WHERE EXISTS (
  SELECT 1 FROM UNNEST(payload.pages)
  WHERE page_name IN ('db_jobskill', 'Profession'));

80 ページ以下のレコードをフィルタする次のレガシー SQL について考えてみます。

#legacySQL
SELECT
  repository.url,
FROM
  [bigquery-public-data:samples.github_nested]
OMIT RECORD IF
  COUNT(payload.pages.page_name) <= 80;

この場合、標準 SQL で ARRAY_LENGTH によるフィルタを使用できます。

#standardSQL
SELECT
  repository.url
FROM
  `bigquery-public-data.samples.github_nested`
WHERE
  ARRAY_LENGTH(payload.pages) > 80;

ARRAY_LENGTH 関数は、レガシー SQL クエリのように、ネストしたフィールド payload.pages.page_name ではなく、繰り返された payload.pages フィールドに直接適用されることに注意してください。

配列と ARRAY サブクエリの詳細については、配列の操作のトピックをご覧ください。

セマンティクスの違い

レガシー SQL と標準 SQL では一部の操作のセマンティクスが異なります。

データ型の自動強制型変換

レガシー SQL と標準 SQL は、特定のデータ型間の強制型変換(自動強制型変換)をサポートしています。たとえば、クエリが入力として FLOAT64 を必要とする関数に渡す場合、BigQuery は INT64 型の値を FLOAT64 に強制型変換します。標準 SQL では、レガシー SQL でサポートしている次の強制型変換をサポートしていません。代わりに、明示的な CAST を使用する必要があります。

  • INT64 リテラルから TIMESTAMP へ。代わりに TIMESTAMP_MICROS(micros_value) を使用します。
  • STRING リテラルから INT64FLOAT64、または BOOL へ。代わりに、CAST(str AS INT64)CAST(str AS FLOAT64)、または CAST(str AS BOOL) を使用します。
  • STRING から BYTES へ。代わりに CAST(str AS BYTES) を使用します。

ランタイム エラー

レガシー SQL の一部の関数は無効な入力に対して NULL を返すため、クエリやデータ内の問題が隠される可能性があります。標準 SQL は、一般により厳格であり、入力が無効である場合にエラーを生成します。

  • すべての数学関数および演算子で、レガシー SQL はオーバーフローをチェックしません。標準 SQL は、オーバーフローのチェックを追加し、計算がオーバーフローした場合にエラーを生成します。これには、+-* 演算子、SUMAVGSTDDEV 集計関数などが含まれます。
  • ゼロで割った場合、標準 SQL はエラーを生成し、レガシー SQL は NULL を返します。標準 SQL のゼロ除算で NULL を返すには、SAFE_DIVIDE を使用します。
  • 入力形式がターゲットの型には無効であるか範囲外である場合、CAST に対し、標準 SQL はエラーを生成し、レガシー SQL は NULL を返します。標準 SQL で無効なキャストに対するエラーを回避するには、SAFE_CAST を使用します。

ネストした繰り返しの結果

標準 SQL を使用して実行されたクエリは、結果の列のネストと繰り返しを保持し、Flatten Results オプションは無効です。ネストしたフィールドのトップレベル列を返すには、構造体の列に対して .* 演算子を使用します。次に例を示します。

#standardSQL
SELECT
  repository.*
FROM
  `bigquery-public-data.samples.github_nested`
LIMIT 5;

繰り返しのネストしたフィールド(STRUCTARRAY)のトップレベルの列を返すには、JOIN を使用して、テーブルの行と繰り返しのネストしたフィールドの要素の外積を取得します。次に例を示します。

#standardSQL
SELECT
  repository.url,
  page.*
FROM
  `bigquery-public-data.samples.github_nested`
JOIN
  UNNEST(payload.pages) AS page
LIMIT 5;

配列と ARRAY サブクエリの詳細については、配列の操作のトピックをご覧ください。

NOT IN 条件と NULL

レガシー SQL は NOT IN 条件での NULL の処理に関して SQL 標準に準拠していませんが、標準 SQL は準拠しています。次のレガシー SQL クエリについて考えてみます。このクエリは、Github サンプル テーブル内にロケーションとして出現していない単語の数を検出します。

#legacySQL
SELECT COUNT(*)
FROM [bigquery-public-data.samples.shakespeare]
WHERE word NOT IN (
  SELECT actor_attributes.location
  FROM [bigquery-public-data.samples.github_nested]
);

このクエリは結果として 163,716 を返します。つまり、Github テーブル内にロケーションとして出現しているのではない単語は 163,716 個あるということです。次の標準 SQL クエリについて考えてみます。

#standardSQL
SELECT COUNT(*)
FROM `bigquery-public-data.samples.shakespeare`
WHERE word NOT IN (
  SELECT actor_attributes.location
  FROM `bigquery-public-data.samples.github_nested`
);

このクエリは、結果として 0 を返します。この違いは、標準 SQL での NOT IN のセマンティクスによるものです。標準 SQL は、条件式の右辺にあるいずれかの値が NULL の場合、NULL を返します。レガシー SQL と同じ結果を得るには、WHERE 句を使用して NULL 値を除外します。

#standardSQL
SELECT COUNT(*)
FROM `bigquery-public-data.samples.shakespeare`
WHERE word NOT IN (
  SELECT actor_attributes.location
  FROM `bigquery-public-data.samples.github_nested`
  WHERE actor_attributes.location IS NOT NULL
);

このクエリは、結果として 163,716 を返します。あるいは、NOT EXISTS 条件を使用するという方法もあります。

#standardSQL
SELECT COUNT(*)
FROM `bigquery-public-data.samples.shakespeare` AS t
WHERE NOT EXISTS (
  SELECT 1
  FROM `bigquery-public-data.samples.github_nested`
  WHERE t.word = actor_attributes.location
);

このクエリも、結果として 163,716 を返します。詳細については、INNOT INEXISTS、およびその他の比較演算子のセマンティクスを説明している、このドキュメントの比較演算子のセクションをご覧ください。

ユーザー定義 JavaScript 関数の違い

ユーザー定義の関数トピックでは、標準 SQL で JavaScript ユーザー定義関数を使用する方法を説明しています。このセクションでは、レガシー SQL と標準 SQL のユーザー定義関数の主な違いを説明します。

クエリテキスト内の関数

標準 SQL では、ユーザー定義関数を個別に指定するのではなく、クエリ本文の一部として CREATE TEMPORARY FUNCTION を使用します。次の標準 SQL クエリについて考えてみます。

#standardSQL
-- Computes the harmonic mean of the elements in 'arr'.
-- The harmonic mean of x_1, x_2, ..., x_n can be expressed as:
--   n / ((1 / x_1) + (1 / x_2) + ... + (1 / x_n))
CREATE TEMPORARY FUNCTION HarmonicMean(arr ARRAY<FLOAT64>)
  RETURNS FLOAT64 LANGUAGE js AS """
var sum_of_reciprocals = 0;
for (var i = 0; i < arr.length; ++i) {
  sum_of_reciprocals += 1 / arr[i];
}
return arr.length / sum_of_reciprocals;
""";

WITH T AS (
  SELECT GENERATE_ARRAY(1.0, x * 4, x) AS arr
  FROM UNNEST([1, 2, 3, 4, 5]) AS x
)
SELECT arr, HarmonicMean(arr) AS h_mean
FROM T;

このクエリは HarmonicMean という JavaScript 関数を定義して、それを T からの配列の列 arr に適用します。

ユーザー定義関数の詳細については、ユーザー定義の関数のトピックをご覧ください。

関数は行ではなく値に作用する

レガシー SQL では、JavaScript 関数はテーブルの行に対して作用します。標準 SQL では、先述の例のように、JavaScript 関数は値に対して作用します。標準 SQL を使用して、JavaScript 関数に行値を渡すには、テーブルと同じ行の型の構造体を取得する関数を定義します。次に例を示します。

#standardSQL
-- Takes a struct of x, y, and z and returns a struct with a new field foo.
CREATE TEMPORARY FUNCTION AddField(s STRUCT<x FLOAT64, y BOOL, z STRING>)
  RETURNS STRUCT<x FLOAT64, y BOOL, z STRING, foo STRING> LANGUAGE js AS """
var new_struct = new Object();
new_struct.x = s.x;
new_struct.y = s.y;
new_struct.z = s.z;
if (s.y) {
  new_struct.foo = 'bar';
} else {
  new_struct.foo = 'baz';
}

return new_struct;
""";

WITH T AS (
  SELECT x, MOD(off, 2) = 0 AS y, CAST(x AS STRING) AS z
  FROM UNNEST([5.0, 4.0, 3.0, 2.0, 1.0]) AS x WITH OFFSET off
)
SELECT AddField(t).*
FROM T AS t;

このクエリは T と同じ行の型で構造体を取得する JavaScript 関数を定義し、foo という追加のフィールドで新しい構造体を作成します。SELECT 文は関数への入力として行 t を渡し、.* を使用して、出力に結果の構造体のフィールドを返します。

標準 SQL の要点

このセクションでは、レガシー SQL と比較した標準 SQL の要点を説明します。

WITH 句を使用したコンポーザビリティ

このページの標準 SQL のいくつかの例では、名前付きサブクエリの抽出や再利用を可能にする WITHを利用します。次に例を示します。

#standardSQL
WITH T AS (
  SELECT x FROM UNNEST([1, 2, 3, 4]) AS x
)
SELECT x / (SELECT SUM(x) FROM T) AS weighted_x
FROM T;

このクエリは、1、2、3、4 の x 値を格納する名前付きサブクエリ T を定義しています。これは、T から x 値を選択し、それらを T 内のすべての x 値の合計で割っています。このクエリは、T のコンテンツがインラインであるクエリと同等です。

#standardSQL
SELECT
  x / (SELECT SUM(x)
       FROM (SELECT x FROM UNNEST([1, 2, 3, 4]) AS x)) AS weighted_x
FROM (SELECT x FROM UNNEST([1, 2, 3, 4]) AS x);

別の例として、複数の名前付きサブクエリを使用する次のクエリについて考えてみます。

#standardSQL
WITH T AS (
  SELECT x FROM UNNEST([1, 2, 3, 4]) AS x
),
TPlusOne AS (
  SELECT x + 1 AS y
  FROM T
),
TPlusOneTimesTwo AS (
  SELECT y * 2 AS z
  FROM TPlusOne
)
SELECT z
FROM TPlusOneTimesTwo;

このクエリは元のデータの一連の変換を定義し、TPlusOneTimesTwo に対する SELECT 文が続いています。このクエリは、計算をインライン化する次のクエリと同等です。

#standardSQL
SELECT (x + 1) * 2 AS z
FROM (SELECT x FROM UNNEST([1, 2, 3, 4]) AS x);

詳細については、このドキュメントの WITHのトピックごご覧ください。

SQL 関数を使用したコンポーザビリティ

標準 SQL はユーザー定義 SQL 関数をサポートしています。ユーザー定義 SQL 関数を使用して、共通の式を定義して、クエリからそれらを参照できます。次に例を示します。

#standardSQL
-- Computes the harmonic mean of the elements in 'arr'.
-- The harmonic mean of x_1, x_2, ..., x_n can be expressed as:
--   n / ((1 / x_1) + (1 / x_2) + ... + (1 / x_n))
CREATE TEMPORARY FUNCTION HarmonicMean(arr ARRAY<FLOAT64>) AS
(
  ARRAY_LENGTH(arr) / (SELECT SUM(1 / x) FROM UNNEST(arr) AS x)
);

WITH T AS (
  SELECT GENERATE_ARRAY(1.0, x * 4, x) AS arr
  FROM UNNEST([1, 2, 3, 4, 5]) AS x
)
SELECT arr, HarmonicMean(arr) AS h_mean
FROM T;

このクエリは HarmonicMean という SQL 関数を定義し、それを T からの配列の列 arr に適用します。

その他の場所のサブクエリ

標準 SQL は SELECT リスト、WHERE 句、および式を期待するクエリ内の他の任意の場所のサブクエリをサポートします。たとえば、2015 年のシアトルの温暖な日の部分を計算する次の標準 SQL クエリについて考えてみます。

#standardSQL
WITH SeattleWeather AS (
  SELECT *
  FROM `bigquery-public-data.noaa_gsod.gsod2015`
  WHERE stn = '994014'
)
SELECT
  COUNTIF(max >= 70) /
    (SELECT COUNT(*) FROM SeattleWeather) AS warm_days_fraction
FROM SeattleWeather;

シアトルの測候所の ID は '994014' です。クエリでは、気温が華氏 70 度または摂氏約 21 度に達した温暖な日数を 2015 年のその測候所で記録された合計日数で割った値に基づいて、温暖な日数を計算しています。

相関サブクエリ

標準 SQL では、サブクエリは相関関係のある列、つまり外部クエリから抽出された列を参照できます。たとえば、次の標準 SQL クエリについて考えてみます。

#standardSQL
WITH WashingtonStations AS (
  SELECT weather.stn AS station_id, ANY_VALUE(station.name) AS name
  FROM `bigquery-public-data.noaa_gsod.stations` AS station
  INNER JOIN `bigquery-public-data.noaa_gsod.gsod2015` AS weather
  ON station.usaf = weather.stn
  WHERE station.state = 'WA' AND station.usaf != '999999'
  GROUP BY station_id
)
SELECT washington_stations.name,
  (SELECT COUNT(*)
   FROM `bigquery-public-data.noaa_gsod.gsod2015` AS weather
   WHERE washington_stations.station_id = weather.stn
   AND max >= 70) AS warm_days
FROM WashingtonStations AS washington_stations
ORDER BY warm_days DESC;

このクエリは、ワシントン州の測候所の名前と気温が華氏 70 度または摂氏約 21 度に達した 2015 年の日数を計算します。SELECT リストにサブクエリがあり、サブクエリは FROM WashingtonStations AS washington_stations という外部スコープからの washington_stations.station_id を参照していることに注意してください。

配列と構造体

ARRAYSTRUCT は標準 SQL の強力な概念です。両方を使用する例として、HackerNews データセット内の各日について、上位 2 つの記事を計算する次のクエリについて考えてみます。

#standardSQL
WITH TitlesAndScores AS (
  SELECT
    ARRAY_AGG(STRUCT(title, score)) AS titles,
    EXTRACT(DATE FROM time_ts) AS date
  FROM `bigquery-public-data.hacker_news.stories`
  WHERE score IS NOT NULL AND title IS NOT NULL
  GROUP BY date)
SELECT date,
  ARRAY(SELECT AS STRUCT title, score
        FROM UNNEST(titles)
        ORDER BY score DESC
        LIMIT 2)
  AS top_articles
FROM TitlesAndScores
ORDER BY date DESC;

WITH 句は 2 つの列を含む TitlesAndScores を定義します。最初の列は構造体の配列で、1 つのフィールドは記事のタイトルで 2 つ目のフィールドはスコアです。ARRAY_AGG 式は各日のこれらの構造体の配列を返します。

WITH 句の後の SELECT 文は ARRAY サブクエリを使用して、score に従ってソートし、各配列内の上位 2 つの記事を返し、次に、日付の降順で結果を返します。

配列と ARRAY サブクエリの詳細については、配列の操作のトピックをご覧ください。配列構造体のリファレンスも参照してください。

フィードバックを送信...