Custom query

This document describes advanced features of Sensors Analytics, involving numerous technical details, and is intended for experienced users. If you have any questions about the document, please contact the Sensors Analytics support team for one-on-one assistance.

For advanced data needs that cannot be met using the existing UI features, we offer a more flexible custom query function. This function supports querying all data from Sensors Analytics using standard SQL, and also includes simple visualizations of the query results.

Note: The current version of the custom query tool is built on the HUE project.

Basic functions

SQL query

Enter the SQL query in the edit box; SQL keyword suggestions and formatting are supported.

Note: The `select *` statement in a custom query can retrieve hidden preset attributes , but cannot retrieve hidden custom attributes .

View query progress

Supports viewing the progress of SQL queries.

Query results

1. Supports data analysis using visualization charts in the query results.

2. Query results support downloading Excel and CSV files. For performance reasons, the maximum number of results displayed on the front end is 1,000, while the maximum number of CSV files downloaded is 1 million. If you need to download more data, please use the query API .

Save SQL

1. Supports saving the current query SQL. The SQL name can be customized, and comments can be added.

2. You can double-click the SQL statement in the saved query list to view and run the SQL statement.

View history

It supports viewing historical SQL query records and searching within those records.

Advanced features

Add to report

Supports quickly saving the results of the current SQL query to a report.

Intermediate table

This is an advanced feature and is not enabled by default. Please contact the operations and maintenance team to enable this feature.

In some business scenarios, the actual data results need to undergo complex calculations before they can be displayed on the result table; in addition, there are some tables with large amounts of data, and statistical queries on them are usually very inefficient; in order to solve the performance problems caused by complex query logic and large data volume, Sensors Data launched the intermediate table function.

Instructions for use

I. Supports creating new queries and intermediate tables, and supports saving existing queries or existing intermediate tables as new intermediate tables.

II. For efficiency reasons, the custom query function has some limitations in the intermediate table, as detailed below:

• Creating an intermediate table is not supported when querying user grouping tables/tag tables across projects.

• Preview mode does not support saving intermediate tables.

• If the query results are ≥ 1, they can be saved as an intermediate table; if the query results exceed 1 million records, only the first 1 million records will be retrieved and saved when saving to the intermediate table (the default is 1 million, and the data volume can be configured by contacting customer service).

• An account can save a maximum of 10 tables under a single project. Intermediate tables are only visible, editable, and deletable by the creator.

• The intermediate table is only used as a temporary table and does not support automatic refresh. Users need to manually refresh the data when using it in Hue.

Usage example

• To create metrics using a session table, you need to add /*SESSION_TABLE_DATE_RANGE=[2018-01-01,2018-01-05]*/ to your query statement.

   /*event表和session表创建中间表示例*/
  
  SELECT sessions_session.event,
         sessions_session.user_id,
         sessions_session.distinct_id,
         sessions_session.`date`,
         EVENTS.showEntrance,
         EVENTS.action_type
  FROM EVENTS
  JOIN sessions_session ON sessions_session.user_id = events.user_id/*SESSION_TABLE_DATE_RANGE=[2018-01-01,2018-01-05]*/
  where events.`date` between '2022-03-21' and '2022-03-27'

Cross-project query

This is an advanced feature and is not enabled by default. Please contact the operations and maintenance team to enable this feature.

Example of a scenario

For example, in the gaming industry , a client might store data for a spot-the-difference game in Project 1, a jigsaw puzzle game in Project 2, and a strategy gacha game in Project 3. The spot-the-difference and jigsaw puzzle games primarily drive traffic to the strategy game. Cross-project queries can then meet the needs of the following scenarios:

Scenario: Viewing a comprehensive data dashboard : Management can view aggregated data such as daily active users, retention, and recharge for three types of games in a single report. The steps to achieve this are as follows:

1. Successfully contacted the client and started the cross-project query function.
2. Understand the English name, project ID, and internal table structure of the project you need.
3. Write a query statement in the custom analysis, execute the query, and save the query results to the report data source.
4. In the report settings, select "New Report," choose the corresponding data source, and configure the report dashboard.

Scenario 2 Data Insights : Analyze the user data from Project 1/Project 2 that has been converted to Project 3. The implementation steps are as follows:

1. Successfully contacted the client and started the cross-project query function.
2. Understand the name of the project you need, as well as the table structure within the project.
3. Write query statements in custom analysis, execute the queries, and download the analysis results to your local machine or create reports.

Instructions for use

We apologize, but cross-project queries are currently in the trial phase, and the process of retrieving the structure of the project table is somewhat cumbersome.

I. How to obtain the English name and project ID of projects you have permission to access : Click "View Project List" in the upper right corner of the page to bring up the project list pop-up window. This allows you to view the project names, project IDs, English names of projects you have permission to access, and the actions you have performed. Clicking "View Project" in the "Actions" column will open the corresponding project page.

II. How to understand the table structure within each project : Currently, this can only be viewed by switching to a specific project and then accessing the custom query page.

III. When is it necessary to specify the project to which a data table belongs ? When writing an SQL statement that can query data across projects, it is necessary to specify the project to which the Sensors Data table being queried belongs.

The events, users, items, groups, tags, and session tables under rawdata can be prefixed with the fixed prefix "horizon" + "_${projectName}_${projectId}" to pin the data table to the project it belongs to. For example, for a project named "production" with project ID "2" (this project prefix serves to restrict the database):

• horizon_production_2.events
• horizon_production_2.users
• horizon_production_2.items
• ...

Other data tables, reports, and intermediate tables each have their own database, which can be accessed via `db.table_name`.

• governor_production.order_detail
• blitzreport_db.daily_operation_report
• hue_medium_table_production.mothers_day_activities
• ...

Usage example: The following is a specific example of cross-project query usage :

• Use UNION/UNION ALL to perform intersection and union operations on the query results of two items and display the results.
  • For example: View the number of app installation events per day for two projects since December.

 SELECT 'production' as project, date, count(1)
FROM horizon_production_2.events
WHERE event like 'AppInstall' and `date` > '2022-12-01'
GROUP BY 1,2
UNION ALL
SELECT 'default' as project, date, count(1)
FROM horizon_default_1.events
WHERE event = 'AppInstall' and `date` > '2022-12-01'
GROUP BY 1,2

• Use the JOIN statement to perform a combined query on the data tables of two projects.
  • For example: View the total number of "AppClicks" performed on the user group "user_group_user_group_1" under the project "projectId=1, projectName=default" in the project "projectId=2, projectName=production" on the same day.

 SELECT count(1) 
FROM horizon_production_2.events events 
INNER JOIN horizon_default_1.user_group_user_group_1 user_group1 ON events.user_id = user_group1.user_id 
WHERE event = 'AppClick' and `date` = CURRENT_DATE()

• • For example: View the data of "date", "distinct_id", "$element_target_url", "$country", and "$utm_campaign" under "event" in the project "projectId=2, projectName=production" in the project "projectId=1, projectName=zwp_001_3".

 SELECT e1.event,
       e2.date,
       e2.distinct_id,
       e2.`$element_target_url`,
       e2.`$country`,
       e2.`$utm_campaign`
FROM horizon_production_2.events e1
LEFT JOIN horizon_zwp_001_3.events e2 ON e1.event = e2.event

Desensitization

This is an advanced feature and is not enabled by default. Please contact the operations and maintenance team to enable this feature.

Based on the configuration of the logged-in user's data permissions and de-identification attributes on the SBP data permission and de-identification attribute configuration page, the de-identification fields are pre-validated in the SQL statements written in the Hue interface for fields that need to be de-identified for event attributes or user attributes.

Currently, there is no distinction between masked display, disabling grouping, and filtering; SQL statements involving masked attributes will directly result in an error.

Current SQL parsing performance:

• Supports SELECT statements, including: Select, From, Where, Group By, and Order By.
• Supports subqueries after FROM and WHERE (consistent with Impala).
• Nested subqueries within a WHERE clause are not currently supported. For example: WHERE column=(SELECT xxx FROM (...) t WHERE...)
• Currently, multiple subqueries at the same level are not supported after FROM, such as: from (...) a, (...) b
• Supports FROM statements with multiple tables or a subquery.

Date filtering

The `date` field represents the date the event occurred, accurate to the day, and can be used to quickly filter data. It's important to note that you should always use the `date` field for filtering, rather than the `time` field.

Due to the special nature of the DATE field, there are some limitations to support for SQL operations and functions. Currently, the supported functions and expressions are:

• The CURRENT_DATE() function returns the current date, for example, 2016-08-23.
• The CURRENT_WEEK() function returns the Monday of the current week, for example, 2016-08-22.
• The CURRENT_MONTH() function returns the first day of the current month, for example, 2016-08-01.
• INTERVAL expressions, such as CURRENT_DATE() - INTERVAL '1' DAY, represent yesterday.

Here are some specific examples:

• Precisely filter data for a specific day

 SELECT COUNT(*) FROM events WHERE date = '2016-01-01'

• Query the data for the day

 SELECT COUNT(*) FROM events WHERE date = CURRENT_DATE()

• Query data for the last 3 days

 SELECT COUNT(*) FROM events WHERE date BETWEEN CURRENT_DATE() - INTERVAL '2' DAY AND CURRENT_DATE()

• Query data from the previous calendar month

 SELECT COUNT(*) FROM events WHERE date BETWEEN CURRENT_MONTH() - INTERVAL '1' MONTH AND CURRENT_MONTH() - INTERVAL '1' DAY

Because the `date` field is a special field designed specifically for fast data filtering, it does not support most time functions. Therefore, if you wish to use other time functions, please use the `time` field instead, for example:

 SELECT datediff(now(), trunc(time, 'DD')), COUNT(*) FROM events WHERE date >= CURRENT_DATE() - INTERVAL '100' day GROUP BY 1

• The number of events aggregated by month after September 1, 2018

 SELECT date_sub(date,dayofmonth(date)-1) the_month,count(*) event_qty 
FROM events WHERE date>'2018-09-01'
GROUP BY the_month ORDER BY the_month;

• The number of events after September 1, 2018, aggregated by week.

 SELECT date_sub(date,mod(dayofweek(date)+5,7)) the_week,count(*) event_qty 
FROM events WHERE date>'2018-09-01'
GROUP BY the_week ORDER BY the_week;

Advanced Options

• Enabling the fast Distinct algorithm can significantly speed up calculations like COUNT(DISTINCT user_id) and supports multiple COUNT(DISTINCT) expressions (in versions 1.17+, multiple COUNT(DISTINCT) expressions are supported even without this comment, but in versions 1.16 and earlier, this comment is required to support multiple COUNT(DISTINCT) expressions). The drawback is that it may produce less precise results. For example:

 SELECT COUNT(DISTINCT user_id) FROM events
WHERE date = CURRENT_DATE() /*ENABLE_APPROX_DISTINCT*/

• Enables dimension dictionary mapping and dimension table association; it is disabled by default. For example:

 SELECT $model FROM events
WHERE date = CURRENT_DATE() /*ENABLE_DIMENSION_DICT_MAPPING*/

• If the SQL query is for data with a specified distinct_id, this option can be used. For example:

 SELECT event, time FROM events
WHERE date = CURRENT_DATE() AND distinct_id='abcdef' /*DISTINCT_ID_FILTER=abcdef*/

• SQL queries are forcibly terminated by the system after 10 minutes by default. To increase the timeout, you can use the following method:

 SELECT * FROM events WHERE date = CURRENT_DATE() LIMIT 1000 /*MAX_QUERY_EXECUTION_TIME=1800*/

• For JOIN queries, you can use the Join Hint to specify the execution mode of the Join, which can be SHUFFLE or BROADCAST. Especially if an out-of-memory error is encountered during execution, consider forcibly specifying SHUFFLE mode.

 SELECT COUNT(*) AS cnt FROM events
JOIN /* +SHUFFLE */ users ON events.user_id = users.id
WHERE date = CURRENT_DATE()

Common Cases

Query the specific actions of a user on a specific day based on the user's distinct_id.

You can directly query using distinct_id:

 SELECT * FROM events WHERE distinct_id = 'wahaha' AND date = '2015-09-10' LIMIT 100

Check the number of users who placed orders between 10:00 AM and 11:00 AM each day.

Use the standard SQL date function EXTRACT to retrieve the hour information.

 SELECT date, COUNT(*) FROM events 
WHERE EXTRACT(HOUR FROM time) IN (10, 11) AND event = 'SubmitOrder'
GROUP BY 1

Query the distribution of user order frequency over a period of time

First, calculate the number of orders placed by each user, and then use the CASE..WHEN syntax to group them.

 SELECT 
    CASE
        WHEN c < 10 THEN '<10'
        WHEN c < 20 THEN '<20'
        WHEN c < 100 THEN '<100'
        ELSE '>100'
    END,
    COUNT(*)
FROM (
    SELECT user_id, COUNT(*) AS c FROM events
    WHERE date BETWEEN '2015-09-01' AND '2015-09-20' AND event = 'SubmitOrder'
    GROUP BY 1
)a 
GROUP BY 1

Query the number of users who performed action A but not action B.

Use LEFT OUTER JOIN to compute the difference set.

 ##twenty one@@

Calculate user usage time

Using analytical functions, the cumulative usage time is estimated based on the interval between two adjacent events for each user. If the interval between two usages exceeds 10 minutes, it is not calculated.

 ##twenty two@@

Get the user's initial behavior attributes

Use the `first_time_value(time, other attributes)` aggregate function to retrieve the relevant attributes when a certain action first occurs.

 ##twenty three@@

Retrieve dimension dictionary uploaded in metadata management

If the attribute has a dimension dictionary, the customer needs to manually add an SQL comment: /*ENABLE_DIMENSION_DICT_MAPPING*/

 ##twenty four@@

Incompatible syntax changes

The custom query engine has been switched to the direct query engine mode, and the underlying query engine version has been upgraded to 4.8, resulting in a small number of incompatible syntax changes.

• The `events.date` field has been changed to a `datetime` type and is no longer supported for number type calculations, such as adding, subtracting, multiplying, or using the `+`, `-`, `*`, or `/` operator. For example:

 /* 支持的语法 */
SELECT * FROM events WHERE date = '2021-03-01' + interval 1 day

/* 不再支持的语法 */
SELECT * FROM events WHERE date - 1 = '2021-03-01'

• When using the `events.date` field in a two-table join, the change in the `date` field's data type can lead to varying degrees of query performance degradation depending on the number of rows joined. This cannot be completely avoided at present, but we will consider rewriting this condition in future versions to avoid calculating the function every time. It is recommended to avoid using the `events.date` field in join operations in performance-sensitive scenarios, based on the business logic. For example:

 /* 有性能损失的用法 */
SELECT 
  * 
FROM 
  (
    FROM 
      * 
    FROM 
      events 
    WHERE 
      date = '2021-01-02'
  ) a 
  LEFT JOIN (
    SELECT 
      * 
    FROM 
      users
  ) b ON a.date = b.birthday

• The alias replacement logic in GROUP BY, HAVING, and ORDER BY is more consistent with standard SQL behavior; that is, aliases are only valid in top-level expressions and not in sub-expressions. For example:

 /* 支持的语法 */
SELECT NOT bool_col AS nb
FROM t
GROUP BY nb
HAVING nb;
 
/* 不再支持的语法 */
SELECT int_col / 2 AS x
FROM t
GROUP BY x
HAVING x > 3;

• A series of reserved fields have been added that cannot be used directly as identifiers. If you need to use them as identifiers, you must enclose them in backticks, for example:

 /* 支持的语法 */
SELECT `position` FROM events
 
/* 不再支持的语法 */
SELECT position FROM events

• The newly added reserved fields include:

Impala Reserved Fields Reference Documentation