Make queries in AIS Data Warehouse

Last updated on March 15, 2024

Overview​

The AccelByte Intelligence Service (AIS) Data Warehouse serves as a pivotal hub for storing and managing relational data, offering a comprehensive solution for efficient storage and seamless querying.

This article walks you through how to use the AIS Data Warehouse and provides detailed examples to show practical application.

note

This article only covers the fundamentals of getting basic data from the AIS Data Warehouse. We recommend that you explore and experiment with queries to determine what will work with your environment and operation needs. For assistance or advice, contact the AccelByte Support Team.

Prerequisites​

Make queries using basic manipulators​

This section covers how to make queries in AIS using basic manipulators.

Query by a specific studio and namespace​

To receive data from a specific namespace, combine the namespace_t and studio_t tables to get data based on specific namespaces and studios. For instance:

SELECT
    count(u.id) AS "Total User"
FROM
    user_t AS u
INNER JOIN
    namespace_t AS n ON n.id = u.namespace_id
INNER JOIN
    studio_t AS s ON s.id = n.studio_id
WHERE
    s.name = 'yourStudioName';

This query will provide valuable insights into the aggregate count of users associated with a particular studio.

Distinguish between production and development data​

The production and development data are stored in the data warehouse. To only get the production data, use the is_production column in the namespace_t table.

SELECT
    count(u.id) AS "Total Users"
FROM
    user_t AS u
INNER JOIN
    namespace_t AS n ON n.id = u.namespace_id
INNER JOIN
    studio_t AS s ON s.id = n.studio_id
WHERE
    n.is_production = TRUE AND s.name = 'yourStudioName';

This query will give the total number of users in your studio's production environment. If you set the is_production as FALSE, then it will show the development data.

Get by Data Lake or Bucket name​

If you have multiple data lakes and want to get data from a specific data lake, use the bucket_name column in the etl_justice_s3_t table.

SELECT
    count(u.id) AS "Total Users"
FROM
    user_t AS u
INNER JOIN
    namespace_t AS n ON n.id = u.namespace_id
INNER JOIN
    etl_justice_s3_t AS ejst ON ejst.namespace_id = n.id
INNER JOIN
    studio_t AS s ON s.id = n.studio_id
WHERE
    s.name = 'yourStudioName' AND
    is_production = TRUE AND
    ejst.bucket_name = 'your-game-prod-bucket-name';

Get data loaded time​

Every data is tied with a job_run_t table, which keeps the details of the AIS Clarity's jobs. Use the finish_ts column to get information on when a data was inserted into the database.

SELECT
    jr.finish_ts AS "Loaded at",
    u.uid AS "User ID",
    n.lname AS "Namespace",
    s.name AS "Studio",
    ejst.bucket_name AS "Bucket Source",
    n.is_production AS "Is Production"
FROM    user_t AS u
INNER JOIN job_run_t AS jr ON jr.id = u.job_run_id
INNER JOIN namespace_t AS n ON n.id = u.namespace_id
INNER JOIN etl_justice_s3_t AS ejst ON ejst.namespace_id = n.id
INNER JOIN studio_t AS s ON s.id = n.studio_id
WHERE s.name = 'yourStudioName'
LIMIT 10;

This query provides 10 rows of users with detailed information such as Loaded at, User ID, Namespace, Studio, Bucket Source, and Is Production.

Last updated on

On this page