Orientação geral para execução de consulta em Query Service

Este documento detalha detalhes importantes a serem conhecidos 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 sobre sintaxe SQL.

Modelos de execução de consulta

O 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 trabalhos maiores e consultas operacionais como parte de um fluxo de trabalho de processamento de dados.

Execução de consulta 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 consulta interativa tem as seguintes limitações:

Por padrão, os resultados de consultas interativas são retornados ao cliente e não são persistentes. Para manter os resultados como um conjunto de dados em Experience Platform, a consulta 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. A execução não interativa significa que Query Service recebe a chamada da API e executa a consulta na ordem em que é recebida. 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 consulta, 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 percorrer 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 colchetes para percorrer 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

Ambos os exemplos de consultas 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:

Quando a coluna é declarada somente 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 posteriores têm usos diferentes nas consultas do Serviço de consulta.

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 cadeia de caracteres entre 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

As aspas duplas (") são usadas 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 posteriores

A aspa invertida ` é usada para escapar nomes de coluna reservados only ao usar a sintaxe de notação de pontos. Por exemplo, como order é uma palavra reservada no SQL, você deve usar aspas invertidas 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 invertidas 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, você precisaria usar a notação de aspas invertidas.

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

As aspas traseiras não sã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

Exibição de informações de tabela

Após se conectar ao Serviço de consulta, você pode ver todas as tabelas disponíveis na plataforma usando os comandos \d ou SHOW TABLES.

Exibição de tabela padrão

O comando \d mostra a exibição padrão PostgreSQL para tabelas de listagem. Um exemplo da saída desse 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 de tabela detalhada

O comando SHOW TABLES é um comando personalizado que fornece informações mais detalhadas sobre as tabelas. Um exemplo da saída desse 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 esquemas na tabela, você pode usar o comando \d {TABLE_NAME}, onde {TABLE_NAME} é o nome da tabela cujas informações de esquema deseja exibir.

O exemplo a seguir mostra as informações de esquema para a tabela luma_midvalues, que seriam vistas 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, você pode 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 consulta.

O exemplo a seguir associaria os dois conjuntos de dados a seguir (your_analytics_table e custom_operating_system_lookup) e criaria 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 Consulta 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.

Cálculos de fuso horário no Serviço de consulta

O Serviço de consulta padroniza os dados persistentes no Adobe Experience Platform usando o formato de carimbo de data e hora UTC. Para obter mais informações sobre como traduzir o requisito de fuso horário de e para um carimbo de data e hora UTC, consulte a seção Perguntas frequentes sobre como alterar o fuso horário de e para um carimbo de data e hora UTC.

Próximas etapas

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

Para obter mais exemplos de consultas que podem ser usadas no Serviço de consulta, leia a documentação do caso de uso a seguir: