Snowflake Information Schema

O Snowflake Information Schema (também conhecido como “Dicionário de dados”) consiste em um conjunto de exibições e funções de tabela definidas pelo sistema que fornecem amplas informações de metadados sobre os objetos criados em sua conta. O Snowflake Information Schema é baseado no Information Schema SQL-92 ANSI, mas com a adição de exibições e funções que são específicas do Snowflake.

O Information Schema é implementado como um esquema chamado INFORMATION_SCHEMA que o Snowflake cria automaticamente em cada banco de dados de uma conta.

Nota

ANSI utiliza o termo “catálogo” para se referir a bancos de dados. Para manter a compatibilidade com o padrão, os tópicos do Snowflake Information Schema usam “catálogo” no lugar de “banco de dados”, quando aplicável. Para todos os efeitos e propósitos, os termos são conceitualmente equivalentes e intercambiáveis.

Neste tópico:

O que é INFORMATION_SCHEMA?

Cada banco de dados criado em sua conta inclui automaticamente um esquema embutido, somente leitura, chamado INFORMATION_SCHEMA. O esquema contém os seguintes objetos:

  • Exibições para todos os objetos contidos no banco de dados, bem como exibições para objetos em nível de conta (ou seja, objetos que não são de banco de dados, como funções, warehouses e bancos de dados)

  • Funções de tabela para dados históricos e de uso em sua conta.

Funções de tabela e exibições Information Schema

Lista de exibições

As exibições no INFORMATION_SCHEMA exibem metadados sobre objetos definidos no banco de dados, assim como metadados para objetos em nível de conta que não são de banco de dados e são comuns em todos os bancos de dados. Cada instância do INFORMATION_SCHEMA inclui:

  • Exibições de padrão ANSI para o banco de dados e objetos em nível de conta que são relevantes para o Snowflake.

  • Exibições específicas do Snowflake para os objetos não padrão a que o Snowflake oferece suporte (estágios, formatos de arquivo etc.).

A menos que seja observado o contrário, as exibições do Snowflake Information Schema são padrão ANSI:

Exibição

Tipo

Específico do Snowflake

Notas

APPLICABLE_ROLES

Conta

COLUMNS

Banco de dados

DATABASES

Conta

ENABLED_ROLES

Conta

EXTERNAL_TABLES

Banco de dados

FILE FORMATS

Banco de dados

FUNCTIONS

Banco de dados

INFORMATION_SCHEMA_CATALOG_NAME

Conta

LOAD_HISTORY

Conta

Dados retidos por 14 dias.

OBJECT_PRIVILEGES

Conta

PACKAGES

Banco de dados

PIPES

Banco de dados

PROCEDURES

Banco de dados

REFERENTIAL_CONSTRAINTS

Banco de dados

REPLICATION_DATABASES

Conta

REPLICATION_GROUPS

Conta

SCHEMATA

Banco de dados

SEQUENCES

Banco de dados

STAGES

Banco de dados

TABLE_CONSTRAINTS

Banco de dados

TABLE_PRIVILEGES

Banco de dados

TABLE_STORAGE_METRICS

Banco de dados

TABLES

Banco de dados

Mostra tabelas e exibições.

USAGE_PRIVILEGES

Banco de dados

Exibe privilégios somente para sequências; para exibir privilégios para outros tipos de objetos, use OBJECT_PRIVILEGES.

VIEWS

Banco de dados

Lista de funções de tabela

As funções de tabela em INFORMATION_SCHEMA podem ser usadas para retornar informações de histórico e de uso em nível de conta para armazenamento, warehouses, logins de usuários e consultas:

Função de tabela

Retenção de dados

Notas

AUTOMATIC_CLUSTERING_HISTORY

14 dias

Os resultados dependem do privilégio MONITOR USAGE. [1]

AUTO_REFRESH_REGISTRATION_HISTORY

14 dias

Os resultados dependem do privilégio MONITOR USAGE. [1]

COMPLETE_TASK_GRAPHS

60 minutos

Resultados retornados somente para a função ACCOUNTADMIN, o proprietário da tarefa (ou seja, a função com o privilégio OWNERSHIP para a tarefa) ou uma função com o privilégio global MONITOR EXECUTION.

COPY_HISTORY

14 dias

Os resultados dependem dos privilégios atribuídos à função atual do usuário.

CURRENT_TASK_GRAPHS

N/A

Resultados retornados somente para a função ACCOUNTADMIN, o proprietário da tarefa (ou seja, a função com o privilégio OWNERSHIP para a tarefa) ou uma função com o privilégio global MONITOR EXECUTION.

DATA_TRANSFER_HISTORY

14 dias

Os resultados dependem do privilégio MONITOR USAGE. [1]

DATABASE_REFRESH_HISTORY

14 dias

Os resultados dependem dos privilégios atribuídos à função atual do usuário.

DATABASE_REFRESH_PROGRESS , DATABASE_REFRESH_PROGRESS_BY_JOB

14 dias

Os resultados dependem dos privilégios atribuídos à função atual do usuário.

DATABASE_REPLICATION_USAGE_HISTORY

14 dias

Resultados retornados somente para a função ACCOUNTADMIN.

DATABASE_STORAGE_USAGE_HISTORY

6 meses

Os resultados dependem do privilégio MONITOR USAGE. [1]

EXTERNAL_FUNCTIONS_HISTORY

14 dias

Os resultados dependem do privilégio MONITOR USAGE. [1]

EXTERNAL_TABLE_FILES

N/A

Os resultados dependem dos privilégios atribuídos à função atual do usuário.

EXTERNAL_TABLE_FILE_REGISTRATION_HISTORY

30 dias

Os resultados dependem dos privilégios atribuídos à função atual do usuário.

LOGIN_HISTORY , LOGIN_HISTORY_BY_USER

7 dias

Os resultados dependem dos privilégios atribuídos à função atual do usuário.

MATERIALIZED_VIEW_REFRESH_HISTORY

14 dias

Os resultados dependem do privilégio MONITOR USAGE. [1]

NOTIFICATION_HISTORY

14 dias

Resultados retornados somente para a função ACCOUNTADMIN, o proprietário da integração (ou seja, a função com o privilégio OWNERSHIP para a integração) ou uma função com o privilégio global USAGE.

PIPE_USAGE_HISTORY

14 dias

Os resultados dependem do privilégio MONITOR USAGE. [1]

POLICY_REFERENCES

N/A

Resultados retornados somente para a função ACCOUNTADMIN.

QUERY_ACCELERATION_HISTORY

14 dias

Os resultados dependem do privilégio MONITOR USAGE. [1]

QUERY_HISTORY , QUERY_HISTORY_BY_*

7 dias

Os resultados dependem dos privilégios atribuídos à função atual do usuário.

REPLICATION_GROUP_REFRESH_HISTORY

14 dias

Os resultados só são retornados para uma função com qualquer privilégio sobre o grupo de replicação ou de failover.

REPLICATION_GROUP_REFRESH_PROGRESS, REPLICATION_GROUP_REFRESH_PROGRESS_BY_JOB

14 dias

Os resultados só são retornados para uma função com qualquer privilégio sobre o grupo de replicação ou de failover.

REPLICATION_GROUP_USAGE_HISTORY

14 dias

Os resultados dependem do privilégio MONITOR USAGE. [1]

REPLICATION_USAGE_HISTORY

14 dias

Resultados retornados somente para a função ACCOUNTADMIN.

REST_EVENT_HISTORY

7 dias

Resultados retornados somente para a função ACCOUNTADMIN.

SEARCH_OPTIMIZATION_HISTORY

14 dias

Os resultados dependem do privilégio MONITOR USAGE. [1]

SERVERLESS_TASK_HISTORY

14 dias

Os resultados dependem do privilégio MONITOR USAGE. [1]

STAGE_DIRECTORY_FILE_REGISTRATION_HISTORY

14 dias

Os resultados dependem dos privilégios atribuídos à função atual do usuário.

STAGE_STORAGE_USAGE_HISTORY

6 meses

Os resultados dependem do privilégio MONITOR USAGE. [1]

TAG_REFERENCES

N/A

Os resultados retornam apenas para a função que tem acesso ao objeto especificado.

TAG_REFERENCES_ALL_COLUMNS

N/A

Os resultados retornam apenas para a função que tem acesso ao objeto especificado.

TASK_DEPENDENTS

N/A

Os resultados retornam somente para a função ACCOUNTADMIN ou proprietário da tarefa (função com privilégio OWNERSHIP para a tarefa).

TASK_HISTORY

7 dias

Resultados retornados somente para a função ACCOUNTADMIN, o proprietário da tarefa (ou seja, a função com o privilégio OWNERSHIP para a tarefa) ou uma função com o privilégio global MONITOR EXECUTION.

VALIDATE_PIPE_LOAD

14 dias

Os resultados dependem dos privilégios atribuídos à função atual do usuário.

WAREHOUSE_LOAD_HISTORY

14 dias

Os resultados dependem do privilégio MONITOR USAGE. [1]

WAREHOUSE_METERING_HISTORY

6 meses

Os resultados dependem do privilégio MONITOR USAGE. [1]

[1] Retorna resultados se a função recebeu o privilégio global MONITOR USAGE; caso contrário, retorna resultados apenas para a função ACCOUNTADMIN.

Notas de uso geral

  • Cada esquema INFORMATION_SCHEMA é somente leitura (ou seja, o esquema e todas as exibições e funções de tabela no esquema não podem ser modificados ou descartados).

  • Consultas sobre exibições INFORMATION_SCHEMA não garantem consistência com respeito a DDL simultâneo. Por exemplo, se um conjunto de tabelas for criado enquanto uma consulta INFORMATION_SCHEMA de longa duração estiver sendo executada, o resultado da consulta pode incluir algumas, nenhuma ou todas as tabelas criadas.

  • A saída de uma exibição ou função de tabela depende dos privilégios concedidos à função atual do usuário. Ao consultar uma exibição INFORMATION_SCHEMA ou função de tabela, somente os objetos para os quais foram concedidos privilégios de acesso à função atual são retornados.

  • Para evitar problemas de desempenho, o seguinte erro é retornado se os filtros especificados em uma consulta INFORMATION_SCHEMA não forem suficientemente seletivos:

    Information schema query returned too much data. Please repeat query with more selective predicates.

  • As exibições específicas do Snowflake estão sujeitas a mudanças. Evite selecionar todas as colunas dessas exibições. Em vez disso, selecione as colunas que você deseja. Por exemplo, se você quiser a coluna name, use SELECT name em vez de SELECT *.

Dica

As exibições Information Schema são otimizadas para consultas que recuperam um pequeno subconjunto de objetos do dicionário. Sempre que possível, maximize o desempenho de suas consultas filtrando os nomes dos esquemas e objetos.

Para obter mais informações e detalhes de uso, consulte o artigo do blog Snowflake Information Schema.

Considerações para substituir comandos SHOW por exibições Information Schema

A exibição INFORMATION_SCHEMA fornece uma interface SQL para as mesmas informações fornecidas pelos comandos SHOW <objetos>. Você pode usar as exibições para substituir esses comandos; no entanto, há algumas diferenças importantes a serem consideradas antes da troca:

Considerações

Comandos SHOW

Exibições do Information Schema

Warehouses

Não é obrigatório executar.

O warehouse deve estar em execução e em uso no momento para consultar as exibições.

Correspondência/filtragem de padrão

Não diferencia maiúsculas e minúsculas (ao filtrar usando LIKE).

Semântica SQL padrão (diferencia maiúsculas e minúsculas). O Snowflake converte automaticamente os identificadores não delimitados por aspas e sem distinção de maiúsculas/maiúsculas em maiúsculas, de forma interna, portanto, nomes de objetos não delimitados por aspas devem ser consultados em maiúsculas nas exibições Information Schema.

Resultados de consultas

A maioria dos comandos SHOW limita os resultados ao esquema atual, por padrão.

As exibições mostram todos os objetos do banco de dados atual/especificado. Para consultar em relação a um esquema específico, é necessário utilizar um predicado de filtro (por exemplo, ... WHERE table_schema = CURRENT_SCHEMA()...). Observe que as consultas do Information Schema sem filtros suficientemente seletivos retornam um erro e não são executadas (consulte Notas gerais de uso neste tópico).

Qualificação dos nomes das funções de tabela e exibições Information Schema em consultas

Ao consultar uma função de tabela ou exibição INFORMATION_SCHEMA, você deve usar o nome qualificado da exibição/função de tabela ou o esquema INFORMATION_SCHEMA deve estar em uso para a sessão.

Por exemplo:

  • Para consultar usando os nomes totalmente qualificados da função de tabela e exibição, na forma de database.information_schema.name:

    SELECT table_name, comment FROM testdb.INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'PUBLIC' ... ;
    
    SELECT event_timestamp, user_name FROM TABLE(testdb.INFORMATION_SCHEMA.LOGIN_HISTORY( ... ));
    
    Copy
  • Para consultar usando os nomes qualificados da função de tabela e exibição, na forma de information_schema.name:

    USE DATABASE testdb;
    
    SELECT table_name, comment FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'PUBLIC' ... ;
    
    SELECT event_timestamp, user_name FROM TABLE(INFORMATION_SCHEMA.LOGIN_HISTORY( ... ));
    
    Copy
  • Para consultar com o esquema INFORMATION_SCHEMA em uso para a sessão:

    USE SCHEMA testdb.INFORMATION_SCHEMA;
    
    SELECT table_name, comment FROM TABLES WHERE TABLE_SCHEMA = 'PUBLIC' ... ;
    
    SELECT event_timestamp, user_name FROM TABLE(LOGIN_HISTORY( ... ));
    
    Copy

    Nota

    Se você estiver usando um banco de dados que foi criado a partir de um compartilhamento e tiver selecionado INFORMATION_SCHEMA como o esquema atual para a sessão, a instrução SELECT pode falhar com o seguinte erro:

    INFORMATION_SCHEMA does not exist or is not authorized

    Se isso ocorrer, selecione um esquema diferente para o esquema atual para a sessão.

Para exemplos mais detalhados, consulte a documentação de referência para cada exibição/função de tabela.