AIS データウェアハウスでクエリを作成する

Last updated on October 23, 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.

注記

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

• You have set up your AIS Data Warehouse connection.
• You have reviewed the AIS Data Warehouse Schema.

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.