Como migrar para o SQL padrão

O BigQuery é compatível com dois dialetos do SQL: padrão e legado. Neste tópico, descrevemos a diferença entre esses dialetos, incluindo a sintaxe, as funções e a semântica e apresentamos exemplos de alguns destaques do SQL padrão.

Comparação entre o SQL legado e o padrão

No BigQuery, as consultas eram executadas com um dialeto do SQL não padrão conhecido como BigQuery SQL. Com o lançamento da versão 2.0, agora o BigQuery é compatível com o SQL padrão, e o BigQuery SQL foi renomeado para SQL legado. O SQL padrão é o dialeto recomendado na consulta de dados armazenados no BigQuery.

Preciso migrar para o SQL padrão?

A migração de SQL legado para SQL padrão é recomendada, mas não é obrigatória. Por exemplo, digamos que você execute diversas consultas com o SQL legado, mas quer usar um recurso do SQL padrão em uma nova consulta. É possível criar novas consultas com SQL padrão, que serão executadas junto com outras que usam o SQL legado.

Ativação do SQL padrão

Você pode optar entre usar o SQL padrão ou o legado ao executar uma consulta no BigQuery. Consulte Ativação do SQL padrão para saber como ativá-lo na IU, CLI, API ou qualquer outra interface do BigQuery.

Vantagens do SQL padrão

O SQL padrão é compatível com o padrão do SQL 2011 e tem extensões que permitem consultas a dados repetidos e aninhados. Ele tem muitas vantagens em relação ao SQL legado, incluindo:

Para ver exemplos onde alguns desses recursos são demonstrados, consulte Particularidades do SQL padrão.

Diferenças dos tipos

Os tipos do SQL legado têm equivalentes no SQL padrão, e vice-versa. Em alguns casos, o nome do tipo é diferente. A tabela a seguir lista cada tipo de dado do SQL legado e seu equivalente no SQL padrão.

SQL legado SQL padrão Observações
BOOL BOOL
INTEGER INT64
FLOAT FLOAT64
STRING STRING
BYTES BYTES
RECORD STRUCT
REPEATED ARRAY
TIMESTAMP TIMESTAMP Veja Diferenças no TIMESTAMP.
DATE DATE O suporte ao DATE no SQL legado é limitado.
TIME TIME O suporte ao TIME no SQL legado é limitado.
DATETIME DATETIME O suporte ao DATETIME no SQL legado é limitado.

Para mais informações sobre o sistema de tipos do SQL padrão, consulte a referência dos tipos de dados do SQL padrão. Para mais informações sobre os tipos de dados no BigQuery, consulte a referência dos tipos de dados do BigQuery.

Diferenças no TIMESTAMP

O SQL padrão tem um intervalo mais restrito de valores válidos de TIMESTAMP em comparação com o SQL legado. No SQL padrão, os valores válidos de TIMESTAMP estão no intervalo de 0001-01-01 00:00:00.000000 a 9999-12-31 23:59:59.999999. Além disso, é possível selecionar os valores de TIMESTAMP mínimo e máximo, como no exemplo abaixo:

#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
);

Nessa consulta, -62135596800000000 é retornado como min_unix_micros, e 253402300799999999, como max_unix_micros.

Se você seleciona uma coluna que contém valores de carimbo de data/hora fora desse intervalo, recebe um erro:

#standardSQL
SELECT timestamp_column_with_invalid_values
FROM MyTableWithInvalidTimestamps;

O seguinte erro é retornado na consulta:

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]

Para corrigi-lo, uma opção é definir e usar uma função definida pelo usuário para filtrar os carimbos de data/hora inválidos:

#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);

Outra opção é usar a função SAFE_CAST com a coluna de carimbo de data/hora. Por exemplo:

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

Em vez de uma string de carimbo de data/hora para valores inválidos, NULL é retornado nessa consulta.

Diferenças de sintaxe

Como fazer o escape de palavras-chave reservadas e identificadores inválidos

No SQL legado, use colchetes [] para fazer o escape de palavras-chave reservadas e identificadores que contêm caracteres inválidos como espaço " " ou hífen "-". No SQL padrão, use acentos graves "`" para fazer o mesmo. Por exemplo:

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

No SQL legado, as palavras-chave reservadas são permitidas em alguns lugares proibidos para o SQL padrão. Por exemplo, na consulta do SQL padrão abaixo, uma falha ocorre devido a um Syntax error:

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

Para corrigir o erro, faça o escape do alias rows usando acentos graves:

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

Para ver uma lista de palavras-chave reservadas e saber o que constitui identificadores válidos, consulte Estrutura léxica.

Nomes de tabelas qualificados por projeto

No SQL legado, use dois-pontos ":" como separador para consultar uma tabela com um nome qualificado por projeto. Por exemplo:

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

No SQL padrão, use ponto final ".". Por exemplo:

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

Se o nome do projeto inclui um domínio como example.com:myproject, use example.com:myproject como nome dele, incluindo os ":".

Decoradores de tabela e funções de caractere curinga

No SQL padrão, não há suporte às funções TABLE_DATE_RANGE, TABLE_DATE_RANGE_STRICT ou TABLE_QUERY.

Consiga a mesma semântica de TABLE_DATE_RANGE e TABLE_QUERY com um filtro na pseudocoluna _TABLE_SUFFIX. Por exemplo, veja a seguinte consulta no SQL legado, que conta o número de linhas de 2010 e 2011 nas tabelas do resumo global do dia (GSOD, na sigla em inglês) da Administração Oceânica e Atmosférica Nacional (NOAA, na sigla em inglês):

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

Esta é uma consulta equivalente no SQL padrão:

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

Para saber mais e ver exemplos que usam TABLE_DATE_RANGE, veja Decoradores de tabela e funções curinga

Vírgulas finais na lista do SELECT

Diferentemente do SQL legado, no SQL padrão não são permitidas vírgulas finais antes da cláusula FROM. Por exemplo, a consulta a seguir é inválida:

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

Para corrigir o erro, remova a vírgula após corpus:

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

Operador vírgula com tabelas

No SQL legado, o operador vírgula "," tem o significado não padrão de UNION ALL quando aplicado a tabelas. No SQL padrão, esse operador tem o significado padrão de JOIN. Por exemplo, veja a seguinte consulta no SQL legado:

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

Isso equivale à seguinte consulta no SQL padrão:

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

Observe também que, no SQL padrão, UNION ALL associa colunas por posição, e não por nome. A consulta acima equivale a:

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

Um uso comum do operador vírgula no SQL padrão é o JOIN com uma matriz. Por exemplo:

#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;

O resultado é o produto do cruzamento da tabela T com elementos de arr. Também é possível expressar a consulta no SQL padrão da seguinte forma:

#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;

Nessa consulta, JOIN tem o mesmo significado do operador vírgula "," que separa T e UNNEST(arr) AS y no exemplo anterior.

Visualizações lógicas

Não é possível consultar uma visualização lógica definida com o SQL legado usando o SQL padrão, e vice-versa, devido a diferenças de sintaxe e semântica entre os dois dialetos. Para fazer isso, é necessário criar uma nova visualização com o SQL padrão, possivelmente com um nome diferente, para substituir a que usa o SQL legado.

Como exemplo, suponha que a visualização V foi definida com o SQL legado da seguinte forma:

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

E que a W foi definida assim:

#legacySQL
SELECT user, action, day
FROM V;

Agora imagine que você executa a seguinte consulta no SQL legado, mas quer migrar essa consulta para o SQL padrão:

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

Um possível caminho de migração é criar novas visualizações usando nomes diferentes. As etapas envolvidas são:

Criar uma visualização denominada V2 usando o SQL padrão com o seguinte conteúdo:

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

Criar uma visualização denominada W2 usando o SQL padrão com o seguinte conteúdo:

#standardSQL
SELECT user, action, day
FROM V2;

Alterar a consulta executada diariamente de modo que ela use o SQL padrão e faça referência à W2:

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

Outra opção é excluir as visualizações V e W e recriá-las usando o SQL padrão com os mesmos nomes. No entanto, nesse caso, é necessário que todas as consultas com referência à V ou W sejam migradas para o SQL padrão ao mesmo tempo.

Comparação das funções

Veja a seguir uma lista parcial das funções do SQL legado e os equivalentes delas no SQL padrão.

SQL legado SQL padrão Observações
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 ou 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) Veja as observações abaixo.
EXACT_COUNT_DISTINCT(x) COUNT(DISTINCT x) Consulte as observações abaixo.
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) Consulte as diferenças abaixo.
TLD(url) NET.PUBLIC_SUFFIX(url) Consulte as diferenças abaixo.
DOMAIN(url) NET.REG_DOMAIN(url) Consulte as diferenças abaixo.
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)

Para mais informações sobre as funções do SQL padrão, consulte o tópico Funções e operadores.

Comparação da função COUNT

Tanto o SQL legado quanto o SQL padrão têm uma função COUNT. No entanto, o comportamento dessa função é diferente, dependendo do dialeto SQL utilizado.

No SQL legado, COUNT(DISTINCT x) retorna uma contagem aproximada. No SQL padrão, uma contagem exata. Para uma contagem aproximada de valores diferentes com execução mais rápida e menos recursos, use APPROX_COUNT_DISTINCT.

Comparação da função URL

Tanto o SQL legado quanto o padrão têm funções de análise de URLs. No SQL legado, essas funções são HOST(url), TLD(url) e DOMAIN(url). No SQL padrão, essas funções são NET.HOST(url), NET.PUBLIC_SUFFIX(url) e NET.REG_DOMAIN(url).

Melhorias em relação às funções do SQL legado

  • Nas funções de URL do SQL padrão, é possível analisar URLs que começam com "//".
  • Quando a entrada não tem o formato da RFC 3986 ou não é um URL, por exemplo, "mailto:?to=&subject=&body=", regras diferentes são aplicadas para analisar a entrada. Em particular, nas funções de URL do SQL padrão, é possível analisar entradas não padrão sem "//", como "www.google.com". Para melhores resultados, recomendamos que as entradas sejam URLs e que estejam de acordo com a RFC 3986.
  • Com a NET.PUBLIC_SUFFIX, os resultados são retornados sem pontos no início. Por exemplo, "com" em vez de ".com". Isso está de acordo com o formato na lista pública de sufixos.
  • Na NET.PUBLIC_SUFFIX e na NET.REG_DOMAIN, há suporte a letras maiúsculas e nomes de domínio internacionalizados. Na TLD e na DOMAIN, isso não acontece, e os resultados podem ser inesperados.

Mudanças menores em casos extremos

  • Quando a entrada não contém nenhum sufixo na lista pública de sufixos, NULL é retornado em NET.PUBLIC_SUFFIX e NET.REG_DOMAIN. Já os valores diferenets de NULL são retornados em TLD e DOMAIN com a melhor estimativa possível.
  • Se a entrada contém apenas um sufixo público sem um marcador precedente, por exemplo, "http://com", o sufixo público é retornado na NET.PUBLIC_SUFFIX, enquanto uma string vazia é retornada na TLD. Da mesma forma, NULL é retornado na NET.REG_DOMAIN, enquanto o sufixo público é retornado na DOMAIN.
  • Para entradas com hosts IPv6, os colchetes não são removidos do resultado em NET.HOST, conforme especificado no RFC 3986.
  • Para entradas com hosts IPv4, NULL é retornado na NET.REG_DOMAIN, enquanto os três primeiros octetos são retornados na DOMAIN.

Exemplos

Na tabela abaixo, a cor de texto cinza indica resultados iguais entre o SQL legado e o padrão.

URL (descrição) HOST NET.HOST TLD NET.PUBLIC _SUFFIX DOMAIN NET.REG_DOMAIN
"//google.com"
(começando com "//")
NULL "google.com" NULL "com" NULL "google.com"
"google.com"
(não padrão; sem "//")
NULL "google.com" NULL "com" NULL "google.com"
"http://user:pass@word@x.com"
(não padrão com múltiplos "@")
"word@x.com" "x.com" ".com" "com" "word@x.com" "x.com"
"http://foo.com:1:2"
(não padrão com múltiplos ":")
"foo.com:1" "foo.com" ".com:1" "com" "foo.com" "foo.com"
"http://x.Co.uk"
(letras maiúsculas)
"x.Co.uk" "x.Co.uk" ".uk" "Co.uk" "Co.uk" "x.Co.uk"
"http://a.b"
(sufixo público não encontrado)
"a.b" "a.b" ".b" NULL "a.b" NULL
"http://com"
(host contém somente um sufixo público)
"com" "com" "" "com" "com" NULL
"http://[::1]"
(host IPv6; sem sufixo público)
"::1" "[::1]" "" NULL "::1" NULL
"http://1.2.3.4"
(host IPv4; sem sufixo público)
"1.2.3.4" "1.2.3.4" "" NULL "1.2.3" NULL

Diferenças no tratamento dos campos repetidos

Um tipo REPEATED no SQL legado é equivalente a um ARRAY desse tipo no SQL padrão. Por exemplo, REPEATED INTEGER é equivalente a ARRAY<INT64> no SQL padrão. A seção a seguir discute algumas das diferenças nas operações em campos repetidos entre SQL padrão e legado.

Elementos NULL e matrizes NULL

No SQL padrão, há suporte a elementos de matriz NULL, mas um erro é gerado se esse elemento NULL aparece no resultado da consulta. Quando há uma coluna de matriz NULL no resultado, ela é armazenada como uma matriz vazia.

Como selecionar campos folha repetidos aninhados

Com o SQL legado, é possível "usar pontos" em um campo repetido aninhado sem precisar considerar onde a repetição ocorre. Em SQL padrão, essa tentativa resulta em erro. Por exemplo:

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

A tentativa de executar essa consulta retorna:

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

Para corrigir o erro e retornar uma matriz de page_names no resultado, use uma subconsulta ARRAY. Por exemplo:

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

Para saber mais sobre matrizes e subconsultas ARRAY, veja o tópico Como trabalhar com matrizes.

Como filtrar campos repetidos

Com o SQL legado, é possível filtrar campos repetidos diretamente usando uma cláusula WHERE. No SQL padrão, você expressa uma lógica similar com um operador vírgula JOIN seguido de um filtro. Por exemplo, veja a seguinte consulta do SQL legado:

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

Nessa consulta, são retornados todos os titles de páginas para os quais o page_name é db_jobskill ou Profession. Expresse uma consulta similar no SQL padrão da seguinte maneira:

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

Uma diferença entre as consultas antigas no SQL legado e no padrão é que, se você desativar a opção Nivelar resultados e executar a consulta no SQL legado, payload.pages.title é REPEATED no resultado da consulta. Para conseguir a mesma semântica no SQL padrão e retornar uma matriz para a coluna title, use uma subconsulta 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;

Nessa consulta, uma matriz de titles é criada, em que page_name é 'db_jobskill' ou 'Profession'. Em seguida, as linhas onde a matriz não corresponde a essa condição são filtradas usando ARRAY_LENGTH(title) > 0.

Para mais informações sobre matrizes, consulte o tópico Trabalho com matrizes.

Estrutura de campos folha aninhados selecionados

No SQL legado, a estrutura dos campos de folha aninhados na lista SELECT é preservada quando a opção Nivelar resultados está desativada, diferentemente do SQL padrão. Por exemplo, veja a seguinte consulta no SQL legado:

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

Nessa consulta, url e has_downloads são retornados dentro de um registro chamado repository quando a opção Nivelar resultados está desativada. Agora, veja a seguinte consulta no SQL padrão:

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

Essa consulta retorna url e has_downloads como colunas de nível superior. Elas não fazem parte de um registro ou estrutura do repository. Para retorná-las como parte de uma estrutura, use o operador STRUCT.

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

Remoção de repetições com FLATTEN

O SQL padrão não tem uma função FLATTEN como o SQL legado, mas é possível conseguir uma semântica similar usando o operador JOIN (vírgula). Por exemplo, veja a seguinte consulta no SQL legado:

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

Expresse uma consulta similar no SQL padrão da seguinte maneira:

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

Ou, de maneira equivalente, use JOIN em vez do operador vírgula ",":

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

Uma diferença importante é que, na consulta com o SQL legado, uma linha é retornada com payload.pages.page_name como NULL se payload.pages estiver vazio. Entretanto, com o SQL padrão, essa linha não é retornada se payload.pages estiver vazio. Para conseguir exatamente a mesma semântica, use LEFT JOIN ou LEFT OUTER JOIN. Por exemplo:

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

Para mais informações sobre matrizes, consulte o tópico Como trabalhar com matrizes. Para saber mais sobre UNNEST, veja o tópico UNNEST.

Como filtrar linhas com OMIT RECORD IF

Com a cláusula OMIT IF do SQL legado, você filtra linhas com base em uma condição que pode ser aplicada a campos repetidos. No SQL padrão, é possível modelar uma cláusula OMIT IF com uma cláusula EXISTS, uma IN ou um filtro simples. Por exemplo, veja a seguinte consulta no SQL legado:

#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');

A consulta análoga no SQL padrão é:

#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');

Aqui, a cláusula EXISTS é avaliada como true se há ao menos um elemento de payload.pages em que o nome da página é 'db_jobskill' ou 'Profession'.

Como alternativa, digamos que a consulta no SQL legado usa 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'));

No SQL padrão, a consulta pode ser expressa usando uma cláusula EXISTS com 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'));

Veja a seguinte consulta no SQL legado, em que os registros com 80 páginas ou menos são filtrados:

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

Nesse caso, é possível usar um filtro com ARRAY_LENGTH no SQL padrão:

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

Observe que a função ARRAY_LENGTH é aplicada diretamente ao campo repetido payload.pages, e não ao campo aninhado payload.pages.page_name como na consulta com SQL legado.

Para saber mais sobre matrizes e subconsultas ARRAY, veja o tópico Como trabalhar com matrizes.

Diferenças de semântica

A semântica de algumas operações do SQL legado são diferentes da do SQL padrão.

Coerções automáticas de tipos de dados

Tanto o SQL legado quanto o SQL padrão aceitam coerções, ou conversões automáticas, entre certos tipos de dados. Por exemplo, no BigQuery, um valor do tipo INT64 é convertido para FLOAT64 se a consulta passa esse valor para uma função que exige FLOAT64 como entrada. No SQL padrão, as coerções abaixo não são aceitas, ao contrário do SQL legado. No lugar delas, é necessário usar um CAST explícito.

  • INT64 literal para TIMESTAMP. Troque por TIMESTAMP_MICROS(micros_value).
  • STRING literal para INT64, FLOAT64 ou BOOL. Troque por CAST(str AS INT64), CAST(str AS FLOAT64) ou CAST(str AS BOOL).
  • STRING para BYTES. Troque por CAST(str AS BYTES).

Erros de execução

Em algumas funções do SQL legado, o NULL é retornado para entradas inválidas, o que pode mascarar problemas nas consultas ou nos dados. Em geral, o SQL padrão é mais rígido, gerando um erro se uma entrada é inválida.

  • No SQL legado, os estouros em funções e operadores matemáticos não são verificados. No SQL padrão, essas verificações são feitas e um erro é gerado se ocorre um estouro no cálculo. Isso inclui os operadores +, - e *, as funções agregadas SUM, AVG e STDDEV e outros.
  • No SQL padrão, um erro é gerado quando há divisão por zero. No legado, NULL é retornado. Para que NULL seja retornado também no SQL padrão, use SAFE_DIVIDE.
  • No SQL padrão, um erro será gerado nos CASTs, se o formato de entrada for inválido ou estiver fora do intervalo do tipo de destino. No legado, NULL é retornado. Para evitar que o erro seja emitido, quando houver um cast inválido no SQL padrão, use SAFE_CAST.

Resultados repetidos aninhados

Nas consultas executadas com o SQL padrão, o aninhamento e a repetição das colunas são preservados no resultado, e a opção Nivelar resultados não tem efeito. Para retornar as colunas de nível superior dos campos aninhados, use o operador .* nas colunas de estrutura. Por exemplo:

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

Para retornar colunas de nível superior de campos aninhados repetidos (ARRAYs de STRUCTs), use um JOIN para ter o produto do cruzamento das linhas da tabela com os elementos do campo aninhado repetido. Por exemplo:

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

Para saber mais sobre matrizes e subconsultas ARRAY, veja o tópico Como trabalhar com matrizes.

Condições NOT IN e NULL

O SQL legado não está em conformidade com o padrão SQL no tratamento de NULL com condições NOT IN, enquanto o padrão está. Pense na seguinte consulta SQL legado, que encontra o número de palavras que não aparecem na tabela de amostras do GitHub como locais:

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

Essa consulta retorna 163.716 como a contagem, indicando que existem 163.716 palavras que não aparecem como locais na tabela do GitHub. Agora, veja a seguinte consulta no SQL padrão:

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

Essa consulta retorna 0 como contagem. A diferença é devido à semântica de NOT IN com SQL padrão, que retorna NULL se qualquer valor no lado direito é NULL. Para alcançar os mesmos resultados que com a consulta do SQL legado, use uma cláusula WHERE para excluir os valores 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
);

Essa consulta retorna 163.716 como contagem. Como alternativa, use uma condição 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
);

Essa consulta também retorna 163.716 como contagem. Para saber mais, veja a seção de operadores de comparação da documentação, em que é explicada a semântica de IN, NOT IN, EXISTS e outros.

Diferenças nas funções JavaScript definidas pelo usuário

No tópico Funções definidas pelo usuário, você encontra informações sobre como usar funções JavaScript definidas pelo usuário com SQL padrão. Veja nesta seção as principais diferenças entre funções definidas pelo usuário no SQL legado e padrão.

Funções no texto da consulta

Com o SQL padrão, você usa CREATE TEMPORARY FUNCTION como parte do corpo da consulta em vez de especificar separadamente as funções definidas pelo usuário. Veja a seguinte consulta no SQL padrão:

#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;

Nessa consulta, uma função JavaScript chamada HarmonicMean foi definida e aplicada à coluna de matriz arr da T.

Para mais informações sobre essas funções, consulte o tópico Funções definidas pelo usuário.

Funções operam em valores, não em linhas

No SQL legado, as funções JavaScript operam nas linhas de uma tabela. No SQL padrão, conforme mostrado no exemplo acima, operam nos valores. Para passar um valor de linha para uma função JavaScript usando o SQL padrão, defina uma função que tenha um estrutura do mesmo tipo de linha que a tabela. Por exemplo:

#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;

Nessa consulta, foi definida uma função JavaScript que usa uma estrutura com o mesmo tipo da linha que T e cria uma nova estrutura com um campo adicional chamado foo. A instrução SELECT passa a linha t como entrada para a função e usa .* para retornar os campos da estrutura resultante na saída.

Particularidades do SQL padrão

Veja nesta seção algumas particularidades do SQL padrão em comparação com o SQL legado.

Composição usando cláusulas WITH

Em alguns dos exemplos de SQL padrão desta página, usamos uma cláusula WITH para possibilitar a extração ou reutilização de subconsultas nomeadas. Por exemplo:

#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;

Nessa consulta, uma subconsulta foi definida com o nome T e contém estes valores x: 1, 2, 3 e 4. Esses valores x da T são selecionados e divididos pela soma de todos os valores x na T. Isso equivale a uma consulta na qual o conteúdo de T está em linha.

#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);

Como outro exemplo, veja esta consulta em que várias subconsultas nomeadas são utilizadas:

#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;

Nessa consulta, uma sequência de transformações dos dados originais foi definida, seguida de uma instrução SELECT na TPlusOneTimesTwo. Isso é equivalente à seguinte consulta, que inclui as computações em linha:

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

Para saber mais, veja o tópico da cláusula WITH na documentação.

Composição usando funções SQL

No SQL padrão, há suporte às funções SQL definidas pelo usuário. Use essas funções para definir expressões comuns e então faça referência a elas na consulta. Por exemplo:

#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;

Nessa consulta, uma função SQL chamada HarmonicMean foi definida e então aplicada à coluna de matriz arr da T.

Subconsultas em outros lugares

No SQL padrão, há suporte às subconsultas na lista do SELECT, na cláusula WHERE e em qualquer lugar da consulta onde uma expressão é esperada. Por exemplo, veja a seguinte consulta no SQL padrão que calcula a fração dos dias quentes em Seattle, em 2015:

#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;

O código da estação meteorológica de Seattle é '994014'. Na consulta, o número de dias quentes é calculado com base nos dias em que a temperatura atingiu 70 graus Fahrenheit, ou aproximadamente 21 graus Celsius, dividido pelo número total de dias registrados pela estação em 2015.

Subconsultas correlacionadas

No SQL padrão, as subconsultas podem fazer referência a colunas correlacionadas, ou seja, colunas originadas de uma consulta externa. Por exemplo, veja a seguinte consulta no SQL padrão:

#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;

Nessa consulta, são processados os nomes das estações meteorológicas no estado de Washington e o número de dias durante 2015 em que a temperatura atingiu 70 graus Fahrenheit, ou aproximadamente 21 graus Celsius. Observe que há uma subconsulta na lista do SELECT, e que a subconsulta faz referência a washington_stations.station_id no escopo externo, a saber FROM WashingtonStations AS washington_stations.

Matrizes e estruturas

ARRAY e STRUCT são conceitos poderosos no SQL padrão. Como um exemplo que inclui ambos, veja a consulta a seguir, que processa os dois principais artigos de cada dia do conjunto de dados HackerNews:

#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;

A cláusula WITH define TitlesAndScores, que contém duas colunas. A primeira é uma matriz de estruturas, em que um campo é um título de artigo, e a segunda é uma pontuação. Na expressão ARRAY_AGG, uma matriz dessas estruturas é retornada para cada dia.

Na instrução SELECT após a cláusula WITH, uma subconsulta ARRAY é usada para classificar e retornar os dois principais artigos dentro de cada matriz de acordo com o score. Em seguida, os resultados são retornados em ordem decrescente por data.

Para saber mais sobre matrizes e subconsultas ARRAY, veja o tópico Como trabalhar com matrizes. Veja também as referências das matrizes e estruturas.

Esta página foi útil? Conte sua opinião sobre:

Enviar comentários sobre…

Precisa de ajuda? Acesse nossa página de suporte.