Exemplos de uso de instruções SQL para consultar dados em um analytical store - Tablestore: Central de Documentação do Alibaba Cloud

Cenário de exemplo

Um fabricante possui 100.000 dispositivos, cada um gerando um conjunto de dados de monitoramento de CPU a cada dois minutos. Para facilitar o gerenciamento e a análise do status dos dispositivos, o fabricante armazena os dados coletados na nuvem. Essa abordagem reduz custos operacionais e permite monitorar a operação dos dispositivos por meio da análise de status.

Nesse cenário, use o recurso de consulta SQL para consultar e analisar rapidamente dados de séries temporais. Considere que os dados de monitoramento estejam armazenados na tabela de séries temporais do Tablestore chamada device, e que a tabela de mapeamento criada para essa tabela de séries temporais se chame device::cpu. A figura a seguir mostra o esquema da tabela de mapeamento criada para a tabela de séries temporais.

Etapa 1: Crie uma tabela de mapeamento para um analytical store

Crie uma tabela de mapeamento no modelo de múltiplos valores para uma tabela de séries temporais usando a instrução CREATE TABLE. Para mais informações, consulte Tabelas de mapeamento para uma tabela de séries temporais em SQL.

Sintaxe

Nota

Os campos _m_name, _data_source, _tags e _time são configurações fixas. Mantenha essas configurações sem modificações.

CREATE TABLE table_name (
  `_m_name` VARCHAR(1024), 
  `_data_source` VARCHAR(1024), 
  `_tags` VARCHAR(1024), 
  `_time` BIGINT(20),
  `column_name ` data_type, 
  ......
  `user_column_namen ` data_type,
  PRIMARY KEY(`_m_name`,`_data_source`,`_tags`,`_time`))ENGINE=AnalyticalStore

Parâmetros

Parâmetro	Descrição
table_name	Nome da tabela de mapeamento, usado para identificá-la. O formato é `time_series_table_name::mapping_table_identifier`. O valor de `time_series_table_name` deve ser igual ao nome real da tabela de séries temporais. Especifique `mapping_table_identifier` conforme suas necessidades de negócio.
column_name	Nome da coluna. O nome da coluna em SQL deve equivaler ao nome da coluna na tabela do Tablestore. Por exemplo, se o nome da coluna na tabela do Tablestore for Aa, o nome da coluna em SQL deve ser Aa, AA, aA ou aa.
data_type	Tipo de dado da coluna, como BIGINT, DOUBLE ou BOOL. O tipo de dado da coluna em SQL deve corresponder ao tipo de dado da coluna na tabela do Tablestore. Para mais informações sobre mapeamentos de tipos de dados, consulte Mapeamentos de tipos de dados em SQL.
ENGINE	Motor de execução utilizado ao consultar dados com a tabela de mapeamento. Se definido como AnalyticalStore, o sistema usa o mecanismo do analytical store. Se não configurado, o sistema não usa o mecanismo do analytical store.

Exemplo

Crie uma tabela de mapeamento no modelo de múltiplos valores chamada device::cpu para a tabela de séries temporais device.

CREATE TABLE `device::cpu` 
(`_m_name` VARCHAR(1024),
 `_data_source` VARCHAR(1024),
 `_tags` VARCHAR(1024),
 `_time` BIGINT(20), 
 `usage_user` BIGINT(20), 
 `usage_system` BIGINT(20),  
 `usage_idle` BIGINT(20), 
 `usage_nice` BIGINT(20), 
 `usage_iowait` BIGINT(20), 
 `usage_irq` BIGINT(20), 
 `usage_softirq` BIGINT(20), 
 `usage_steal` BIGINT(20),
 `usage_guest` BIGINT(20), 
 `usage_guest_nice` BIGINT(20),
 `one_hour_stamp` BIGINT(20),
 `one_minute_stamp` BIGINT(20),
 PRIMARY KEY(`_m_name`,`_data_source`,`_tags`,`_time`))  ENGINE=AnalyticStore;

Etapa 2: Consultar dados executando instruções SQL

Depois de criar uma tabela de mapeamento para um analytical store, execute a instrução SELECT para consultar dados nesse repositório. Há latência de sincronização durante a transferência de dados da tabela de séries temporais para o analytical store. Ao consultar os dados mais recentes, alguns registros podem ainda não ter sido sincronizados.

Sintaxe

Nota

A prioridade de execução das cláusulas na instrução SELECT é: cláusula WHERE > cláusula GROUP BY > cláusula ORDER BY > cláusulas LIMIT e OFFSET.

SELECT
 select_expr [, select_expr] ...
 [FROM table_references]
 [WHERE where_condition]
 [GROUP BY groupby_condition]
 [ORDER BY order_condition]
 [LIMIT {[offset,] row_count | row_count OFFSET offset}]

Parâmetros

Parâmetro	Obrigatório	Descrição
select_expr	Sim	Nome da coluna ou expressão de coluna no formato `column_name[, column_name][, column_exp],...`. Use expressões de coluna para especificar as colunas desejadas na consulta. Ao utilizar expressões de coluna, siga estas regras: Use o caractere curinga asterisco (*) para consultar todas as colunas. Também é possível usar a cláusula WHERE para definir a condição de consulta. Especifique o nome da coluna para indicar qual delas consultar. Aplique funções de agregação para coletar estatísticas e realizar análises. Use funções de tempo para agrupar por período temporal.
table_references	Sim	Nome da tabela de mapeamento criada para a tabela de séries temporais.
where_condition	Não	Cláusula WHERE combinável com diferentes condições para implementar funcionalidades específicas. Use operadores relacionais no formato `column_name operator value [AND \| OR] [column_name operator value]` para consultar dados que atendam às condições especificadas. Ao usar where_condition, observe as seguintes regras: Use expressões simples construídas com operadores aritméticos e relacionais. Use expressões combinadas construídas com operadores lógicos. Combine funções de tempo com a coluna _time da tabela de séries temporais para selecionar um intervalo de tempo. Use funções de séries temporais com a coluna _tags para consultar dados com valores de tag específicos.
groupby_condition	Não	Cláusula GROUP BY para consultas agrupadas, utilizável em conjunto com funções de séries temporais. Ao aplicar groupby_condition, atente-se aos pontos abaixo: Agrupe os dados por campo. Use funções de séries temporais para segmentar dados por tempo.
order_condition	Não	Cláusula ORDER BY para ordenação, no formato `column_name [ASC \| DESC][,column_name [ASC \| DESC],...]`. Defina o método de ordenação com ASC ou DESC. O padrão é ordem crescente (ASC). Especifique vários campos para ordenação. Geralmente, order_condition é usado com a cláusula LIMIT para limitar o número máximo de linhas retornadas.
row_count	Não	Número máximo de linhas a serem retornadas nesta consulta.
offset	Não	Deslocamento de dados para esta consulta. O valor padrão é 0.

Exemplos

Exemplo 1: Consulte o valor máximo da coluna usage_irq e o valor máximo da coluna usage_softirq para todos os dispositivos por dia, no período de 2023-01-05 05:14:00 a 2023-01-07 09:14:00.

Importante
O fuso horário de unix_timestamp_micros("2023-01-05 05:14:00.000000") corresponde ao fuso horário do sistema (para a China, é o horário de Pequim UTC+8).
```
SELECT time_bin(_time,"1day"), max(usage_irq),max(usage_softirq) 
FROM `device::cpu` 
WHERE _time > unix_timestamp_micros("2023-01-05 05:14:00.000000") 
AND _time < unix_timestamp_micros("2023-01-07 09:14:00.000000") 
GROUP BY 1 ORDER BY 1;
```
Exemplo 2: Obtenha a média de todos os valores na coluna usage_nice para todos os períodos de dispositivos com host_50625 e arquitetura de CPU x64.
```
SELECT avg(usage_nice) 
FROM `device::cpu` 
WHERE _data_source = "host_50625" 
AND tag_value_at(_tags,"arch") = "x64";
```
Exemplo 3: Consulte a quantidade de linhas de dados, a média dos valores na coluna usage_user e a média dos valores na coluna usage_system para todos os dispositivos entre 2023-01-05 05:14:00 e 2023-01-07 09:14:00.
```
SELECT count(*),avg(usage_idle),avg(usage_system) 
FROM `device::cpu` 
WHERE _time > unix_timestamp_micros("2023-01-05 05:14:00.000000") 
AND _time < unix_timestamp_micros("2023-01-07 09:14:00.000000");
```
Exemplo 4: Agrupe os dados de CPU por semana e, em seguida, agrupe cada conjunto por hora para calcular o desvio padrão dos valores na coluna usage_user de cada grupo.
```
SELECT week(from_unixtime_micros(_time)) as week,time_bin(_time,"1h"), stddev(usage_user) 
FROM `device::cpu` 
GROUP BY 1,2 ORDER BY 1,2;
```

Exemplo 5: Consulte os valores das colunas usage_user, usage_system e usage_nice no último ponto de cada intervalo de duas horas para todos os dispositivos com host_50625, no período de 2023-01-05 05:14:00 a 2023-01-07 09:14:00.

SELECT time_bin(_time,"2h"), max_by(usage_user,_time),
max_by(usage_system,_time),max_by(usage_nice,_time) 
FROM `device::cpu` 
WHERE _time > unix_timestamp_micros("2023-01-05 05:14:00.000000") 
AND _time < unix_timestamp_micros("2023-01-07 09:14:00.000000")
AND _data_source = 'host_50625'
GROUP BY 1 ORDER BY 1;

Exemplo 6: Retorne o tempo, mês, dia da semana, hora, minuto, segundo e microssegundo quando o valor da coluna usage_user for 100, dentro do intervalo de 2023-01-05 05:14:00 a 2023-01-07 09:14:00.

SELECT from_unixtime_micros(_time) as time, 
monthname(from_unixtime_micros(_time)) as monthname,
dayname(from_unixtime_micros(_time)) as dayname,
hour(from_unixtime_micros(_time)) as hour,
minute(from_unixtime_micros(_time)) as minute,
second(from_unixtime_micros(_time)) as second,
microsecond(from_unixtime_micros(_time)) as microsecond 
FROM `device::cpu` 
WHERE _time > unix_timestamp_micros("2023-01-05 05:14:00.000000") 
AND _time < unix_timestamp_micros("2023-01-07 09:14:00.000000") 
AND usage_user = 100 LIMIT 100;