Use TDP to Query Athena SQL Tables

The SQL Search page allows you to run SQL queries on Amazon Athena SQL tables to access your Tetra Data.

From the SQL SQL Search page, you can do any of the following:

  • Filter the list of available SQL tables
  • View the SQL table structure and definitions
  • View the first 100 rows of content
  • Generate custom queries
  • Export query results to a CSV file

๐Ÿ“˜

NOTE

(For customer hosted deployments only) Using the SQL Search page will result in additional costs in your AWS account, because the page runs Amazon Athena queries. Query costs depend on the amount of data stored in your environment. For more information, see Amazon Athena Pricing in the AWS documentation.

Access the SQL Search Page

  1. Sign in to the TDP.
  2. In the left navigation menu, choose SQL Search. The SQL Search page appears.

SQL Search page

Filter the List of Available SQL Tables

To filter the list of available SQL tables on the SQL Search page, enter the name (or a part of the name) of the table that you're looking for in the upper left Search for Table search bar.

๐Ÿ“˜

New File Version Behavior

When you process a file using a protocol, the fileโ€™s data resides in that protocol's tables. If you later process the same file or an updated version of the file using a new protocol, that fileโ€™s data is stored in the new protocol's tables, leaving the original protocol tables untouched. The Tetra Data Platform (TDP) does not delete the original filesโ€™ data from the original protocol tables.

View the Table Structure and Definitions

To view the structure of a table, do the following:

  1. On the SQL Search page, find the SQL table that you want to review.
  2. Select the hamburger menu icon next to the table name. A menu appears.
  3. Choose View Table Info. The Table Definition dialog appears.

Table Definition dialog

Table Definition Information

Each Table Definition includes the following information:

  • Table name and time when table was created, last updated, and last accessed
  • Names of table columns and their data types
  • Parameters (such as the type of file or classification) and whether it contains encrypted data

View the First 100 Rows in the Table

To view the first 100 rows in a table, do the following:

  1. On the SQL Search page, find the SQL table that you want to review.
  2. Select the hamburger menu icon next to the table name. A menu appears.
  3. Choose SELECT First 100 Rows. The SQL query automatically generates and runs. Query results display in the Results table below the SQL query editor.

Create Custom Queries

๐Ÿšง

WARNING

To help improve query performance, make sure that you run delete operations in small batches. Deleting a high number of files at once slows down your SQL queries.

To create a custom query:

  1. Determine which table or tables you want to query. To review information about the SQL table structure and details, see View Athena SQL Tables and Structure.
  2. Select SQL Search from the list of menu options again to return to the SQL query editor and results page.
  3. Enter your query in the SQL Query Editor text box. For syntax assistance, select the SQL syntax documentation link above the text box.
  4. Select the Run query button. If the query is valid, the results display in the Results section. If there is a issue with the query, an error display in the Results section.

๐Ÿ“˜

NOTE

The Athena SQL number value is A 64-bit signed double-precision floating point number. The range is 4.94065645841246544e-324d to 1.79769313486231570e+308d, positive or negative.

Export Results to a CSV File

To export SQL query results to a file in CSV format, do the following:

  1. Run a query on the SQL Query page.
  2. At the top of the Results section, select the Export as CSV icon (shows as an arrow pointing into a bin). The query results download as a CSV file to your local machine.