Orientações gerais para a execução da consulta em Query Service

Este documento detalha detalhes importantes a saber ao gravar consultas no Adobe Experience Platform Query Service.

Para obter informações detalhadas sobre a sintaxe SQL usada em Query Service, leia a documentação da sintaxe SQL.

Modelos de execução de query

Adobe Experience Platform Query Service tem dois modelos de execução de consulta: interativa e não interativa. A execução interativa é usada para desenvolvimento de consultas e geração de relatórios em ferramentas de business intelligence, enquanto a não interativa é usada para tarefas e consultas operacionais maiores como parte de um fluxo de trabalho de processamento de dados.

Execução de query interativa

As consultas podem ser executadas interativamente enviando-as por meio da interface do usuário Query Service ou por meio de um cliente conectado. Ao executar Query Service por meio de um cliente conectado, uma sessão ativa é executada entre o cliente e Query Service até que a consulta enviada retorne ou expire.

A execução de query interativa tem as seguintes limitações:

Por padrão, os resultados de consultas interativas são retornados ao cliente e not são persistentes. Para persistir os resultados como um conjunto de dados em Experience Platform, a query deve usar a sintaxe CREATE TABLE AS SELECT.

Execução de consulta não interativa

Consultas enviadas por meio da API Query Service são executadas de forma não interativa. Execução não interativa significa que Query Service recebe a chamada da API e executa a consulta na ordem em que é recebida. As consultas não interativas sempre resultam na geração de um novo conjunto de dados em Experience Platform para receber os resultados ou na inserção de novas linhas em um conjunto de dados existente.

Acesso a um campo específico em um objeto

Para acessar um campo dentro de um objeto em sua query, você pode usar a notação de pontos (.) ou a notação de colchetes ([]). A instrução SQL a seguir usa a notação de pontos para atravessar o objeto endUserIds até o objeto mcid.

SELECT endUserIds._experience.mcid
FROM {ANALYTICS_TABLE_NAME}
WHERE endUserIds._experience.mcid IS NOT NULL
AND TIMESTAMP = to_timestamp('{TARGET_YEAR}-{TARGET_MONTH}-{TARGET_DAY}')
LIMIT 1

A instrução SQL a seguir usa a notação de colchete para atravessar o objeto endUserIds até o objeto mcid.

SELECT endUserIds['_experience']['mcid']
FROM {ANALYTICS_TABLE_NAME}
WHERE endUserIds._experience.mcid IS NOT NULL
AND TIMESTAMP = to_timestamp('{TARGET_YEAR}-{TARGET_MONTH}-{TARGET_DAY}')
LIMIT 1

Ambas as consultas de exemplo acima retornam um objeto nivelado, em vez de um único valor:

              endUserIds._experience.mcid   
--------------------------------------------------------
 (48168239533518554367684086979667672499,"(ECID)",true)
(1 row)

O objeto endUserIds._experience.mcid retornado contém os valores correspondentes para os seguintes parâmetros:

• id
• namespace
• primary

Quando a coluna é declarada apenas para o objeto, ela retorna o objeto inteiro como uma string. Para exibir somente a ID, use:

SELECT endUserIds._experience.mcid.id
FROM {ANALYTICS_TABLE_NAME}
WHERE endUserIds._experience.mcid IS NOT NULL
AND TIMESTAMP = to_timestamp('{TARGET_YEAR}-{TARGET_MONTH}-{TARGET_DAY}')
LIMIT 1

     endUserIds._experience.mcid.id 
----------------------------------------
 48168239533518554367684086979667672499
(1 row)

Aspas

Aspas simples, aspas duplas e aspas traseiras têm usos diferentes em consultas do Serviço de query.

Aspas simples

A aspa simples (') é usada para criar cadeias de texto. Por exemplo, ele pode ser usado na instrução SELECT para retornar um valor de texto estático no resultado e na cláusula WHERE para avaliar o conteúdo de uma coluna.

A consulta a seguir declara um valor de texto estático ('datasetA') para uma coluna:

SELECT 
  'datasetA',
  timestamp,
  web.webPageDetails.name
FROM {ANALYTICS_TABLE_NAME}
WHERE TIMESTAMP = to_timestamp('{TARGET_YEAR}-{TARGET_MONTH}-{TARGET_DAY}')
LIMIT 10

A consulta a seguir usa uma sequência de caracteres de aspas simples ('homepage') em sua cláusula WHERE para retornar eventos de uma página específica.

SELECT 
  timestamp,
  endUserIds._experience.mcid.id
FROM {ANALYTICS_TABLE_NAME}
WHERE web.webPageDetails.name = 'homepage'
AND TIMESTAMP = to_timestamp('{TARGET_YEAR}-{TARGET_MONTH}-{TARGET_DAY}')
LIMIT 10

Aspas duplas

A aspa dupla (") é usada para declarar um identificador com espaços.

A consulta a seguir usa aspas duplas para retornar valores de colunas especificadas quando uma coluna contém um espaço em seu identificador:

SELECT
  no_space_column,
  "space column"
FROM
( SELECT 
    'column1' as no_space_column,
    'column2' as "space column"
)

Aspas atrás

A aspa traseira ` é usada para omitir nomes de colunas reservados somente ao usar sintaxe de notação de pontos. Por exemplo, como order é uma palavra reservada no SQL, você deve usar aspas retroativas para acessar o campo commerce.order:

SELECT 
  commerce.`order`
FROM {ANALYTICS_TABLE_NAME}
WHERE TIMESTAMP = to_timestamp('{TARGET_YEAR}-{TARGET_MONTH}-{TARGET_DAY}')
LIMIT 10

Aspas antigas também são usadas para acessar um campo que começa com um número. Por exemplo, para acessar o campo 30_day_value, seria necessário usar a notação de aspas.

SELECT
    commerce.`30_day_value`
FROM {ANALYTICS_TABLE_NAME}
WHERE TIMESTAMP = to_timestamp('{TARGET_YEAR}-{TARGET_MONTH}-{TARGET_DAY}')
LIMIT 10

As aspas traseiras são não necessárias se você estiver usando a notação de colchetes.

 SELECT
  commerce['order']
 FROM {ANALYTICS_TABLE_NAME}
 WHERE TIMESTAMP = to_timestamp('{TARGET_YEAR}-{TARGET_MONTH}-{TARGET_DAY}')
 LIMIT 10

Exibindo informações da tabela

Depois de se conectar ao Serviço de query, você pode ver todas as tabelas disponíveis no Platform usando os comandos \d ou SHOW TABLES.

Exibição de tabela padrão

O comando \d mostra a exibição padrão do PostgreSQL para listar tabelas. Um exemplo da saída deste comando pode ser visto abaixo:

             List of relations
 Schema |       Name      | Type  |  Owner   
--------+-----------------+-------+----------
 public | luma_midvalues  | table | postgres
 public | luma_postvalues | table | postgres
(2 rows)

Exibição detalhada da tabela

SHOW TABLES é um comando personalizado que fornece informações mais detalhadas sobre as tabelas. Um exemplo da saída deste comando pode ser visto abaixo:

       name      |        dataSetId         |     dataSet    | description | resolved 
-----------------+--------------------------+----------------+-------------+----------
 luma_midvalues  | 5bac030c29bb8d12fa992e58 | Luma midValues |             | false
 luma_postvalues | 5c86b896b3c162151785b43c | Luma midValues |             | false
(2 rows)

Informações do esquema

Para exibir informações mais detalhadas sobre os schemas na tabela, você pode usar o comando \d {TABLE_NAME}, onde {TABLE_NAME} é o nome da tabela cujas informações de schema deseja exibir.

O exemplo a seguir mostra as informações do schema para a tabela luma_midvalues, que seria vista usando \d luma_midvalues:

                         Table "public.luma_midvalues"
      Column       |             Type            | Collation | Nullable | Default 
-------------------+-----------------------------+-----------+----------+---------
 timestamp         | timestamp                   |           |          | 
 _id               | text                        |           |          | 
 productlistitems  | anyarray                    |           |          | 
 commerce          | luma_midvalues_commerce     |           |          | 
 receivedtimestamp | timestamp                   |           |          | 
 enduserids        | luma_midvalues_enduserids   |           |          | 
 datasource        | datasource                  |           |          | 
 web               | luma_midvalues_web          |           |          | 
 placecontext      | luma_midvalues_placecontext |           |          | 
 identitymap       | anymap                      |           |          | 
 marketing         | marketing                   |           |          | 
 environment       | luma_midvalues_environment  |           |          | 
 _experience       | luma_midvalues__experience  |           |          | 
 device            | device                      |           |          | 
 search            | search                      |           |          | 

Além disso, é possível obter mais informações sobre uma coluna específica ao anexar o nome da coluna ao nome da tabela. Isso seria gravado no formato \d {TABLE_NAME}_{COLUMN}.

O exemplo a seguir mostra informações adicionais para a coluna web e seria chamado usando o seguinte comando: \d luma_midvalues_web:

                 Composite type "public.luma_midvalues_web"
     Column     |               Type                | Collation | Nullable | Default 
----------------+-----------------------------------+-----------+----------+---------
 webpagedetails | luma_midvalues_web_webpagedetails |           |          | 
 webreferrer    | web_webreferrer                   |           |          | 

Associar conjuntos de dados

É possível unir vários conjuntos de dados para incluir dados de outros conjuntos de dados em sua query.

O exemplo a seguir uniria os dois conjuntos de dados a seguir (your_analytics_table e custom_operating_system_lookup) e criará uma instrução SELECT para os 50 principais sistemas operacionais por número de exibições de página.

Query

SELECT 
  b.operatingsystem AS OperatingSystem,
  SUM(a.web.webPageDetails.pageviews.value) AS PageViews
FROM your_analytics_table a 
     JOIN custom_operating_system_lookup b 
      ON a._experience.analytics.environment.operatingsystemID = b.operatingsystemid 
WHERE TIMESTAMP >= TO_TIMESTAMP('2018-01-01') AND TIMESTAMP <= TO_TIMESTAMP('2018-12-31')
GROUP BY OperatingSystem 
ORDER BY PageViews DESC
LIMIT 50;

Resultados

Desduplicação

O Serviço de query oferece suporte à desduplicação de dados ou à remoção de linhas duplicadas dos dados. Para obter mais informações sobre desduplicação, leia o Guia de desduplicação do serviço de consulta.

Próximas etapas

Ao ler este documento, você foi apresentado a algumas considerações importantes ao gravar consultas usando Query Service. Para obter mais informações sobre como usar a sintaxe SQL para gravar suas próprias consultas, leia a documentação da sintaxe SQL.

Para obter mais amostras de queries que podem ser usadas no Serviço de query, leia os guias em Adobe Analytics sample queries, Adobe Target sample queries ou ExperienceEvent sample queries.