ÃÛ¶¹ÊÓƵ

Transcript

ÃÛ¶¹ÊÓƵ Experience Platform Query Service is a set of tools that allows you to query the contents of Experience Platform datasets. To better understand how Query Service works in context, it’s helpful to know how data is ingested and stored in Platform at a high level. Platform has multiple ingestion patterns depending on where data is coming from, but in most cases the data is ingested into one or more datasets as columns in rows. These datasets are stored in the data lake, which is a centralized data store that’s populated to each Experience Platform sandbox. The data lake is one of Experience Platform’s key data stores where customer Experience data can be further processed and sent to downstream applications like ÃÛ¶¹ÊÓƵ Technologies or third-party systems. Experience Platform’s other key data stores are the Identity Graph and Profile Store, which are separate from the data lake. These are where Experience Platform stores and manages customer identities and profiles respectively. Why is all this important for Query Service? Well, everything that you can do in Query Service revolves around what lives in the data lake specifically. You can’t use it to query identity graphs or profiles since those are accessed using other tools like segmentation and the Identity Graph Viewer. If your organization has purchased the Data Distiller add-on package for an Experience Platform application, you can go beyond read-only operations and use Query Service to actively transform and insert data into the data lake. As a foundational core service in Experience Platform, Query Service is included in all platform-based applications, specifically ÃÛ¶¹ÊÓƵ Realtime Customer Data Platform, ÃÛ¶¹ÊÓƵ Journey Optimizer, ÃÛ¶¹ÊÓƵ Customer Journey Analytics, and ÃÛ¶¹ÊÓƵ Mix Modeler. Since Experience Platform is extremely flexible when it comes to the range of sources and structures of data it can ingest, Query Service is equally flexible in the kinds of questions about that data. However, your access to specific Query Service capabilities may vary depending on which products and add-ons your organization has purchased. Let’s go over how this breaks down and introduce you to the service’s core features while we’re at it. In the platform interface, you can access Query Service features by selecting Queries in the left navigation. From the landing page, select Create Query and you’ll be brought to the Query Editor. Using the Editor, you can write custom queries for your platform datasets using standard SQL. For example, writing a simple SELECT query, you can see I provide the name of a platform dataset as part of the FROM clause, indicating which dataset I want to query. This is called the table name of the dataset, which you can find by going to the Datasets tab and opening the details of the dataset in question. To run this query, I’ll select the Play icon, and you can see that the results of the query are immediately output in the area below. By executing ad hoc queries like this, you can quickly explore and validate customer experience data. In addition to standard SQL syntax, there are also special ÃÛ¶¹ÊÓƵ-defined functions that help you more flexibly access and organize experience data. Using these functions, you can group related events into sessions, leverage pathing contexts like the previous page view for a given event, analyze time gaps between specific event types, Query service also provides authentication credentials, letting you execute queries from external clients if preferred. Any platform-based application user can execute queries like this, provided they’ve been granted Manage Queries permission from an administrator. At any given time, your organization’s users can execute a maximum of four queries concurrently. You can also use queries to write results back to another dataset for further actioning and analysis. By outputting query results to datasets, analysts and data engineers can clean, transform, and enrich customer experience data in addition to exploring and validating through ad hoc queries. Queries that write their results to datasets can also be scheduled and centrally managed as batch jobs. To use these queries, your organization must have previously purchased the Data Distiller add-on package for a platform-based application. With the Data Distiller add-on, the Queries overview screen is a little different. Designed to help you get the most of its features, you’ll find handy links to documentation, a curated list of recommended accelerators, and inspiring use case examples. Plus, it highlights key metrics tailored specifically for Data Distiller, giving you quick insights at a glance. The Data Distiller batch queries count shows how many queries are running automatically or on schedule, giving you a clear picture of how much data transformation is being streamlined The Compute Hours metric tells you the total processing time used by all your queries, helping you keep track of your usage and make sure you’re staying within your licensing limits. And the Data Exploratory queries show how actively you’re working with your data, running ad hoc queries to validate results, explore data, or troubleshoot issues. Within the Query Editor interface, you’ll see all datasets and tables in the selected database. You can expand each one to see related child tables, or quickly search to find the specific queries you need. Simply click on a table or field to insert it directly into the editor, making it easier and faster to build your queries. So that was a brief overview of Query Service and its core capabilities, including which capabilities are available to all users of platform-based applications versus those that are exclusive to the Data Distiller add-on package. We only scratch the surface when it comes to the many ways Query Service can be used, so we strongly encourage you to experiment with your own queries and to check out our other tutorials and technical documentation to learn more about different use cases and processes. Thanks for watching!

Transcript

In this video, you’ll learn how to explain data usage patterns and query service.

Consuming data through Query Service can happen in a couple of ways to different mechanisms. We already discussed the ability to launch queries to the Query Editor UI which is available inside ÃÛ¶¹ÊÓƵ Experience Platform. The ability to use external tools and support Postgres like PSQL does with a command line editor. The ability to use BI-tools and also the ability to use the Customer Journey Analytics Module, which will bring Analysis’ Workspace to ÃÛ¶¹ÊÓƵ Experience Platform. Additionally, query service offers an HTTP API, which allows brands to consume query service from inside their own applications. Let’s zoom in a bit deeper on each of those. First of all, the Query Editor which is available natively inside ÃÛ¶¹ÊÓƵ Experience platform, has the goal of helping business analysts to its query developments, analysis and exploration. The Query Editor is an interactive tool for developing and testing queries. It offers a set of interesting features, like automatic syntax highlighting, SQL keyword auto-complete, table and field auto-complete, and also error detection. It’s an interactive environment which means that you can’t close your browser when executing a query as it’s query will then be dropped. Your browser window needs to remain active for the total duration of the query. Next is the PSQL Client. The PSQL Client can and should be used for query development, analysis and exploration as well. PSQL is a command line interface which is installed together with Postgres and it makes it easy to connect from an external environment to Query Service for testing and development purposes. Many brands use BI-solutions to deliver data driven inside and an easy to consume visual representation. Thanks to query service, brands no longer have to implement and maintain lengthy data import transformation and export processes. And can now easily connect from their preferred BI-environments directly to ÃÛ¶¹ÊÓƵ Experience Platform. These BI-solutions can consume data sets from platform but aren’t intended to refresh dashboards by consuming full data sets every couple of minutes. The preferred and scale level way of consuming data from a BI-solution is to consume data sets that have been populated to a scheduled queries on data sets that have been prepared by in CTAS commands. Query Service also offers an HTTP API, which offers brands the ability to run queries and get query results as part of a brands operational process. These APIs are fully documented on this link. Lastly, a couple of important tips and best practices. When working with XDM Schema fields, the way to do that is to use either dot-notation or the bracket-notation. Interactive Query Execution has a couple of requirements. First of all, the maximum time an Interactive Query can run is 10 minutes. It will also return a maximum of 50 000 rows. And the brand can have a maximum of 5 concurrent queries.

The limit of 50 000 can be bypassed by specifying the limit parameter as part of the query. But even then, the maximum timeout remains 10 minutes. These limits apply to the Query Editor UI, PSQL and BI-solutions. These limits do not apply to the Query Service HTTP API which has no limits, and which handles all requests on a first come, first serve basis and captures results in a data sets. Query Service offers brands multiple ways of interacting with data and as such, caters for every need. The Query Editor UI in ÃÛ¶¹ÊÓƵ Experience Platform makes query development a lot easier. With CTAS, insights can be written back to Platform and can be consumed by Data Science Workspace, Real Time Customer Profile and BI-solutions. And finally, the Query Service API allows brands to interact with Query Service from inside an application. With that, you should now be able to explain the data usage patterns in Query Service.