Join SLS Logstore with MySQL for federated query analysis - Simple Log Service: Central de Documentação do Alibaba Cloud

Antes de começar

Logs coletados em um Logstore. Coleta de dados.
Índices criados para os campos de log. Criar um índice.
Banco de dados MySQL disponível. Criar um banco de dados e uma conta.

Cenário

Uma empresa de jogos coleta dois tipos de dados: logs de jogo dos usuários (ações, alvos, HP, MP, rede, pagamentos, cliques, códigos de status, IDs de usuário) no SLS e metadados dos usuários (gênero, data de registro, região) no MySQL. O objetivo é associar ambos os conjuntos de dados para análise operacional.

O SLS oferece suporte a consultas federadas entre um Logstore e armazenamentos externos (MySQL, OSS). Use a sintaxe SQL JOIN para combinar logs de jogos com metadados de usuários e gravar os resultados em um armazenamento externo. Scenario

Procedimento

No banco de dados MySQL, crie uma tabela para as propriedades do usuário.

Crie uma tabela join_meta para armazenar as propriedades do usuário (ID, apelido, gênero, idade, data de registro, saldo, região).

CREATE TABLE `join_meta` ( 
  `uid` int(11) NOT NULL DEFAULT '0', 
  `user_nick` text, 
  `gender` tinyint(1) DEFAULT NULL, 
  `age` int(11) DEFAULT NULL, 
  `register_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 
  `balance` float DEFAULT NULL, 
  `region` text, PRIMARY KEY (`uid`) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Adicione endereços IP à lista de permissões.

ApsaraDB RDS for MySQL

Adicione os blocos CIDR 100.104.0.0/16, 11.194.0.0/16 e 11.201.0.0/16 à lista de permissões. Para mais informações, consulte Configurar uma lista de permissões de endereços IP.

Self-managed MySQL on ECS

Adicione regras de grupo de segurança para permitir o acesso dos blocos CIDR 100.104.0.0/16, 11.194.0.0/16 e 11.201.0.0/16. Para mais informações, consulte Adicionar uma regra de grupo de segurança.

AnalyticDB for MySQL

Adicione os blocos CIDR 100.104.0.0/16, 11.194.0.0/16 e 11.201.0.0/16 à lista de permissões. Para mais informações, consulte Configurar uma lista de permissões.

Crie um ExternalStore.

Faça login no servidor CLI e execute touch para criar o arquivo /home/shell/config.json. Adicione o conteúdo abaixo ao arquivo config.json. Substitua region, vpc-id, host, port, username, password, db e table pelos valores correspondentes.

{
    "externalStoreName":"sls_join_meta_store",
    "storeType":"rds-vpc",
    "parameter":{
        "region":"cn-qingdao",
        "vpc-id":"vpc-m5eq4irc1pucp*******",
        "host":"rm-bp1******rm76.mysql.rds.aliyuncs.com",
        "port":"3306",
        "username":"user",
        "password":"****",
        "db":"scmc",
        "table":"join_meta"
    }
}

Parâmetro	Descrição
`externalStoreName`	Nome do ExternalStore. Use apenas letras minúsculas.
`storeType`	Valor fixo: `rds-vpc`.
`region`	Região do banco de dados. Para RDS MySQL, defina region como a região da instância RDS. Para ADB MySQL, defina region como a região da instância ADB. Para MySQL auto-gerenciado em ECS em uma VPC, defina region como a região da instância ECS. Importante A instância do banco de dados (RDS, ADB ou ECS) deve estar na mesma região do projeto SLS.
`vpc-id`	ID da VPC do banco de dados. Para RDS MySQL, defina vpc-id como o ID da VPC. Para ADB MySQL, defina vpc-id como o ID da VPC. Para MySQL auto-gerenciado em ECS, defina vpc-id como o ID da VPC.
`host`	Em bancos de dados VPC, o backend resolve o host para um IP durante a criação e não o atualiza automaticamente. Se o IP da instância mudar (por exemplo, após uma migração), atualize ou recrie a tabela externa. Para RDS MySQL em uma VPC, defina host como o endpoint interno da instância RDS. Para ADB MySQL em uma VPC, defina host como o endpoint interno da instância ADB. Para MySQL auto-gerenciado em ECS em uma VPC, defina host como o IP privado da instância ECS. Para bancos de dados acessíveis pela internet, use o endpoint público ou o IP.
`port`	Número da porta do banco de dados. Para RDS MySQL, defina port como a porta da instância RDS. Para ADB MySQL, defina port como a porta da instância ADB. Para MySQL auto-gerenciado em ECS, defina port como a porta do serviço MySQL.
`username`	Nome de usuário para acesso ao banco de dados.
`password`	Senha do nome de usuário especificado.
`db`	Nome do banco de dados.
`table`	Nome da tabela do banco de dados. Formatos suportados: `table_name`. Exemplo: `test`. `schema_name.table_name`. Exemplo: `public.test`.

Execute o comando a seguir para criar o ExternalStore. Substitua <project-name> pelo nome do seu projeto.

aliyunlog log create_external_store --project_name=<project-name> --config="file:///home/shell/config.json"

Use a sintaxe JOIN para executar uma consulta federada.

Faça login no console do Simple Log Service.
Na seção Projects, clique em no projeto desejado.
Na guia Log Storage > Logstores, clique em no Logstore desejado.

Execute uma instrução de consulta e análise.

Associe o Logstore ao MySQL correspondendo o campo de log userid ao campo uid do banco de dados.

Analise a distribuição de gênero dos usuários ativos.

* | 
select 
  case gender when 1 then 'Male' else 'Female' end as gender, 
  count(1) as pv 
from log l join sls_join_meta_store u on l.userid = u.uid 
group by gender 
order by pv desc

Join analysis

Analise a atividade dos usuários por região.

* | 
select region , count(1) as pv 
from log l join sls_join_meta_store u on l.userid = u.uid 
group by region 
order by pv desc

User activity by region

Analise os padrões de gastos por gênero.

* | 
select 
  case gender when 1 then 'Male' else 'Female' end as gender, 
  sum(money) as money 
from log l join sls_join_meta_store u on l.userid = u.uid 
group by gender 
order by money desc

Salve os resultados da consulta no banco de dados MySQL.

No MySQL, crie uma tabela report para armazenar as contagens de visualizações de página (PV) por minuto.

CREATE TABLE `report` ( 
  `minute` bigint(20) DEFAULT NULL, 
  `pv` bigint(20) DEFAULT NULL 
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Crie um ExternalStore para a tabela report repetindo a Etapa 3.

Na página de consulta e análise do Logstore, execute a seguinte instrução para salvar os resultados na tabela report. sls_report_store é o nome do ExternalStore da tabela report.

* | insert into sls_report_store select __time__- __time__ % 300 as min, count(1) as pv group by min

Visualize os resultados no MySQL:

mysql> select * from report;
+------------+-------+
| minute     | pv    |
+------------+-------+
| 1526448600 |  3000 |
| 1526448540 |  9900 |
| 1526448780 |  3100 |
| 1526448480 |  5400 |
| 1526448720 |  3000 |
| 1526448960 |  3000 |
| 1526448900 |  3000 |
| 1526449080 |  3000 |
| 1526449140 |  3000 |
| 1526448660 |  2900 |
| 1526449260 |  3000 |
+------------+-------+

Antes de começar

Cenário

Procedimento

ApsaraDB RDS for MySQL

Self-managed MySQL on ECS

AnalyticDB for MySQL