Understanding data usage patterns with Query Service

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 Adobe 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 Adobe 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 Adobe 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 Adobe 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 Adobe 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.