SQL Tables

Database

Each organization’s primary database name will be the normalized organization slug (organization short name). The normalization will convert all dashes to underscores. For example, if the org slug is “demo-uat”, the normalized form will be “demo_uat“.

Table

SQL tables are created based on the TetraScience IDS designed for each specific instrument or use case. On the Tetra Data Platform, you can find all the SQL tables here.

Let's start from the root table. We will use the IDS below to help explain the concept. Notice that we exclude the "datacubes" field (we will cover that in the next section). The diagram may be a little small to view so please click on it to enlarge it.

1.1 IDS to Athena table diagram1.1 IDS to Athena table diagram

1.1 IDS to Athena table diagram

1.2 "root" table1.2 "root" table

1.2 "root" table

1.3 "method_instrument_detector_channels" table1.3 "method_instrument_detector_channels" table

1.3 "method_instrument_detector_channels" table

📘

Notice

For each table name, the <IDS name>_<IDS version>_ prefix is omitted in this graph for simplicity.

The above diagram shows an example of how an IDS file’s data is converted into Athena tables. The general rules are:

  • For each key, as long as the value is not an array of objects or array of arrays, is a column in the root table
  • If the key contains an array of objects or array of arrays, then we create a separate table and link it with the parent table using parent_uuid
  • If the key contains an array of literals, then it’s still a column in the current table

Each table name has the format like this:
<IDS name>_<IDS version>_<root field name>_<child field name 1>_<child field name 2>_…

For example, an Empower v2 “injection” table will be (“injection” is the root field in the IDS):
lcuv_empower_v2_injection

On TetraScience data integration platform, you can find all the SQL tables here. And when you click on any of them, you can see the columns this table has. Again, for column names, all the nonalphanumeric characters are converted to underscores due to the character restriction of S3 and Athena (S3 and Athena still allow some special characters, but for simplicity, TetraScience just convert all special characters to underscores).

Depends on the tool you are using, you may declare the database to connect to upfront. Then in the query, you can use the table names directly. Otherwise, you may need to add the database name in your query. It will be like: "demo_uat"."lcuv_empower_v2_injection".

datacubes tables

Now let's talk about datacubes field. Depending on the instrument you are using, you may or may not have datacubes tables in your SQL table list.

datacubes table creation is handled differently than other Athena table creation. You will have two tables:

  • <IDS name>_<IDS version>_datacubes
  • <IDS name>_<IDS version>_datacubes_data

For example, an Empower v2 “datacubes” table will be:

  • lcuv_empower_v2_datacubes
  • lcuv_empower_v2_datacubes_data

datacubes table contains all the metadata and each measure’s name and unit and each dimension’s name and unit. As illustrated below, each measure’s name and unit and each dimension’s name and unit will be an individual column. These columns will be auto-generated depends on the number of measures and the number of dimensions defined in the IDS file.

datacubes_data table, on the other hand, stores all the value of the measures and the dimensions. And same as name and unit, each measure’s value and each dimension’s value will be an individual column.

1.4 IDS datacubes to Athena tables diagram1.4 IDS datacubes to Athena tables diagram

1.4 IDS datacubes to Athena tables diagram

1.5 "datacubes" table1.5 "datacubes" table

1.5 "datacubes" table

1.6 "datacubes_data" table1.6 "datacubes_data" table

1.6 "datacubes_data" table

📘

Notice

For each table name, the <IDS name>_<IDS version>_ prefix is omitted in this graph for simplicity.

metadata table

You probably see there is a metadata_v1 table in your SQL table list.

1.3 metadata_v1 table1.3 metadata_v1 table

1.3 metadata_v1 table

That table is created by TetraScience, which contains platform metadata from all files in the data lake. All files in TetraScience data lake will have some metadata attached by TetraScience. We call it platform metadata. You can also view it by clicking the "Preview" button on the JSON files on "Search Files" page on the platform. In metadata_v1, you will see file_id, file_path, ids_type, ids_version as the columns.


Did this page help you?