Considerações sobre o design de tabelas¶
Este tópico fornece as práticas recomendadas, diretrizes gerais e considerações importantes ao projetar e gerenciar tabelas.
Neste tópico:
Tipos de dados de data/hora para colunas¶
Ao definir colunas para conter datas ou carimbos de data/hora, a Snowflake recomenda escolher um tipo de dados de data ou carimbo de data/hora em vez de um tipo de dados de caracteres. O Snowflake armazena dados de DATE e TIMESTAMP com mais eficiência do que VARCHAR, resultando em um melhor desempenho das consultas. Escolha um tipo de dados de data ou carimbo de data/hora apropriado, dependendo do nível de granularidade requerido.
Restrições de integridade referencial¶
As restrições de integridade referencial no Snowflake são informativas e, com exceção de NOT NULL, não são aplicadas. Restrições diferentes de NOT NULL são criadas como desabilitadas.
No entanto, as restrições fornecem metadados valiosos. As chaves primárias e as chaves estrangeiras permitem que os membros de sua equipe de projeto se orientem para o design do esquema e se familiarizem com a forma como as tabelas se relacionam.
Além disso, a maioria das ferramentas de business intelligence (BI) e exibição importam as definições de chave estrangeira com as tabelas e constroem as condições de junção adequadas. Esta abordagem economiza seu tempo e é potencialmente menos propensa a erros do que alguém mais tarde ter que adivinhar como unir as tabelas e depois configurar manualmente a ferramenta. Basear junções nas chaves primárias e estrangeiras também ajuda a garantir a integridade do design, uma vez que a interpretação das junções não fica a cargo de diferentes desenvolvedores. Algumas ferramentas de BI e exibição também aproveitam as informações de restrições para reescrever as consultas em formas mais eficientes, por exemplo, com eliminação de junções.
Especifique uma restrição ao criar ou modificar uma tabela usando os comandos CREATE | ALTER TABLE … CONSTRAINT.
No exemplo a seguir, a instrução CREATE TABLE para a segunda tabela (salesorders) define uma restrição de chave estrangeira fora de linha que faz referência a uma coluna na primeira tabela (salespeople):
create or replace table salespeople (
sp_id int not null unique,
name varchar default null,
region varchar,
constraint pk_sp_id primary key (sp_id)
);
create or replace table salesorders (
order_id int not null unique,
quantity int default null,
description varchar,
sp_id int not null unique,
constraint pk_order_id primary key (order_id),
constraint fk_sp_id foreign key (sp_id)
references salespeople(sp_id)
);
Consulte a função GET_DDL para recuperar uma instrução DDL que possa ser executada para recriar a tabela especificada. A instrução inclui as restrições atualmente definidas para uma tabela.
Por exemplo:
select get_ddl('table', 'mydb.public.salesorders');
+-----------------------------------------------------------------------------------------------------+
| GET_DDL('TABLE', 'MYDATABASE.PUBLIC.SALESORDERS') |
|-----------------------------------------------------------------------------------------------------|
| create or replace TABLE SALESORDERS ( |
| ORDER_ID NUMBER(38,0) NOT NULL, |
| QUANTITY NUMBER(38,0), |
| DESCRIPTION VARCHAR(16777216), |
| SP_ID NUMBER(38,0) NOT NULL, |
| unique (SP_ID), |
| constraint PK_ORDER_ID primary key (ORDER_ID), |
| constraint FK_SP_ID foreign key (SP_ID) references MYDATABASE.PUBLIC.SALESPEOPLE(SP_ID) |
| ); |
+-----------------------------------------------------------------------------------------------------+
Alternativamente, recupere uma lista de todas as restrições de tabela por esquema (ou para todos os esquemas em um banco de dados) consultando a exibição Exibição TABLE_CONSTRAINTS no Information Schema.
Por exemplo:
select table_name, constraint_type, constraint_name
from mydb.information_schema.table_constraints
where constraint_schema = 'PUBLIC'
Order by table_name;
+-------------+-----------------+-----------------------------------------------------+
| TABLE_NAME | CONSTRAINT_TYPE | CONSTRAINT_NAME |
|-------------+-----------------+-----------------------------------------------------|
| SALESORDERS | UNIQUE | SYS_CONSTRAINT_fce2257e-c343-4e66-9bea-fc1c041b00a6 |
| SALESORDERS | FOREIGN KEY | FK_SP_ID |
| SALESORDERS | PRIMARY KEY | PK_ORDER_ID |
| SALESORDERS | UNIQUE | SYS_CONSTRAINT_bf90e2b3-fd4a-4764-9576-88fb487fe989 |
| SALESPEOPLE | PRIMARY KEY | PK_SP_ID |
+-------------+-----------------+-----------------------------------------------------+
Quando definir uma chave de clustering¶
A especificação de uma chave de clustering não é necessária para a maioria das tabelas. O Snowflake realiza o ajuste automático através do mecanismo de otimização e do microparticionamento. Em muitos casos, os dados são carregados e organizados em micropartições por data ou carimbo de data/hora, e são consultados ao longo da mesma dimensão.
Quando você deve especificar uma chave de chave de clustering para uma tabela? Primeiro, observe que o clustering de uma pequena tabela normalmente não melhora significativamente o desempenho da consulta.
Para conjuntos de dados maiores, você pode considerar a especificação de uma chave de chave de clustering para uma tabela quando:
A ordem na qual os dados são carregados não corresponde à dimensão pela qual são mais comumente consultados (por exemplo, os dados são carregados por data, mas os relatórios filtram os dados por ID). Se seus scripts ou relatórios existentes consultarem os dados tanto por datas como por ID (e potencialmente uma terceira ou quarta coluna), você poderá ver alguma melhoria de desempenho criando uma chave de clustering de várias colunas.
Query Profile indica que uma porcentagem significativa do tempo de duração total para consultas típicas da tabela é gasta em verificação. Isto se aplica a consultas que filtram uma ou mais colunas específicas.
Note que o reclustering regrava os dados existentes em uma ordem diferente. A ordenação anterior é armazenada por 7 dias para fornecer uma proteção Fail-safe. O reclustering de uma tabela incorre em custos que se correlacionam com o tamanho dos dados que são reordenados.
Para obter mais informações, consulte Clustering automático.
Quando especificar os comprimentos das colunas¶
O Snowflake comprime eficazmente os dados das colunas; portanto, criar colunas maiores que o necessário tem um impacto mínimo no tamanho das tabelas de dados. Da mesma forma, não há diferença de desempenho de consulta entre uma coluna com uma instrução de comprimento máximo (por exemplo, VARCHAR(16777216)) e uma precisão menor.
Entretanto, quando o tamanho dos dados de sua coluna for previsível, a Snowflake recomenda definir um comprimento de coluna apropriado, pelas seguintes razões:
É mais provável que as operações de carregamento de dados detectem problemas tais como colunas carregadas fora de ordem, por exemplo, uma cadeia de 50 caracteres carregada erroneamente em uma coluna VARCHAR(10). Tais problemas causam erros.
Quando o comprimento da coluna não é especificado, algumas ferramentas de terceiros podem antecipar o consumo do valor do tamanho máximo, o que pode se traduzir em aumento do uso da memória do lado do cliente ou comportamento incomum.
Armazenamento de dados semiestruturados em uma coluna VARIANT vs. nivelamento da estrutura aninhada¶
Se você ainda não tem certeza dos tipos de operações que deseja realizar em seus dados semiestruturados, a Snowflake recomenda armazenar os dados em uma coluna VARIANT, por enquanto. Para dados que são em sua maioria regulares e usam apenas tipos nativos (cadeias de caracteres e números inteiros), os requisitos de armazenamento e o desempenho de consulta para operações com dados relacionais e dados em uma coluna VARIANT é muito semelhante.
Para melhor remoção e menor consumo de armazenamento, a Snowflake recomenda nivelar seus dados de objetos e chaves em colunas relacionais separadas se seus dados semiestruturados incluírem:
Datas e carimbos de data/hora, especialmente datas e carimbos de data/hora que não sejamISO 8601, como valores de cadeias de caracteres
Números dentro de cadeias de caracteres
Matrizes
Valores não nativos, como datas e carimbos de data/hora, são armazenados como cadeias de caracteres quando carregados em uma coluna VARIANT, de modo que as operações com esses valores podem ser mais lentas e também consumir mais espaço do que quando armazenadas em uma coluna relacional com o tipo de dados correspondente.
Se você conhece seus casos de uso para os dados, realize testes em um conjunto de dados típico. Carregue o conjunto de dados em uma coluna VARIANT de uma tabela. Use a função FLATTEN para extrair os objetos e chaves que você planeja consultar em uma tabela separada. Execute um conjunto típico de consultas em ambas as tabelas para ver qual estrutura oferece o melhor desempenho.
Conversão de uma tabela permanente em uma tabela transitória ou vice-versa¶
Atualmente, não é possível mudar uma tabela permanente para uma tabela transitória usando o comando ALTER TABLE. A propriedade TRANSIENT é definida na criação da tabela e não pode ser modificada.
Da mesma forma, não é possível mudar diretamente uma tabela transitória para uma tabela permanente.
Para converter uma tabela permanente existente em uma tabela transitória (ou vice-versa), preservando os dados e outras características como padrões de coluna e privilégios concedidos, você pode criar uma nova tabela e usar a cláusula COPY GRANTS e depois copiar os dados:
CREATE TRANSIENT TABLE my_new_table LIKE my_old_table COPY GRANTS; INSERT INTO my_new_table SELECT * FROM my_old_table;
Se você quiser preservar todos os dados, mas não os privilégios e outras características concedidas, pode usar CREATE TABLE AS SELECT (CTAS), por exemplo:
CREATE TRANSIENT TABLE my_transient_table AS SELECT * FROM mytable;
Outra maneira de fazer uma cópia de uma tabela (mas mudar o ciclo de vida de permanente para transitório) é CLONE a tabela, por exemplo:
CREATE TRANSIENT TABLE foo CLONE bar COPY GRANTS;Partições antigas não são afetadas (elas não se tornam transitórias), mas as novas partições adicionadas ao clone seguirão o ciclo de vida transitório.
Não se pode clonar uma tabela transitória para uma tabela permanente.