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

Este documento detalha detalhes importantes a serem conhecidos ao escrever consultas no Adobe Experience Platform Query Service.

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

Modelos de execução de consulta

Adobe Experience Platform Query Service O tem dois modelos de execução de consulta: interativo e não interativo. 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 do Query Service Interface do usuário 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 atinja o tempo limite.

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 persistente. Para manter os resultados como um conjunto de dados no Experience Platform, o query deve usar o CREATE TABLE AS SELECT sintaxe.

Execução de consulta não interativa

Consultas enviadas por meio do Query Service As APIs 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 no Experience Platform para receber os resultados ou a 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 notação de colchetes ([]). A instrução SQL a seguir usa a notação de pontos para percorrer endUserIds objeto até o mcid objeto.

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 endUserIds objeto até o mcid objeto.

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 resultado endUserIds._experience.mcid contém os valores correspondentes para os seguintes parâmetros:

• id
• namespace
• primary

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

Aspas simples (') é usado para criar cadeias de texto. Por exemplo, ela pode ser usada na variável SELECT para retornar um valor de texto estático no resultado, e na variável 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 entre aspas ('homepage') na 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 (") é usado 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 ` é usado para evitar nomes de colunas reservadas somente ao usar a sintaxe da notação de pontos. Por exemplo, desde 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, seria necessário 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 são não necessário se 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

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

Exibição de tabela padrão

A variável \d mostra o padrão PostgreSQL exibição 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

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 schemas na tabela, você pode usar o \d {TABLE_NAME} , onde {TABLE_NAME} é o nome da tabela cujas informações de esquema você deseja exibir.

O exemplo a seguir mostra as informações do esquema para a variável luma_midvalues tabela, 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, 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 o 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 cria um SELECT instrução para os 50 principais sistemas operacionais por número de exibições de página.

Consulta

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 a 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 o Seção de 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 Query Service. Para obter mais informações sobre como usar a sintaxe SQL para gravar suas próprias consultas, leia o Documentação da 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:

• Insights do Analytics
• Criar um relatório de tendências de eventos
• Exibir um relatório acumulado de um visitante
• Listar as exibições de página de um usuário
• Listar visitantes pelo número de exibições de página