Use TDP to Query Athena SQL Tables

This page describes how an Admin user can use the TetraScience Data Platform (TDP) to query Athena SQL tables.
To query the available Athena SQL tables, use the SQL Search page.

📘

NOTE

(For single-tenant 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. In the TDP, click the Hamburger icon at the top left corner of the page to expand the TDP menu options (or hover over the list of icons to display the menu options).
47

Hamburger icon

  1. After clicking the Hamburger Menu icon, the icon changes to an X. Select SQL Search from the list of menu options that appears on the left side of the page.
1843

SQL query editor and results page

From the SQL query editor and results page, you can:

  • 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 results to a CSV file

Filter the list of available SQL Tables

To filter the list of SQL tables so that you can find the one you are interested in type the name (or a part of the name) of the table in the text box near the top of the list.

View the Table Structure and Definitions

To view the structure of a table:

  1. From the left side of the page, click the menu button next to the table you want to see the definition for.
238

SQL Query table options

  1. Click View table info to open the Table definition pane which shows this 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
780

Table definition pane

  1. When you are finished reviewing the table data, click Close.

View the First 100 Rows in the Table

To view the first 100 rows in a table:

  1. From the left side of the page, click the menu button next to the table for which you want to select the table rows.
  2. Click SELECT first 100 rows. The SQL query is automatically generated and begins to run.
  3. Once completed, query results display in the Results table below the SQL query editor.
902

SQL Query results

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.

📘

NOTE

You can copy a table name by finding it in the list on the left side of the screen, clicking on it, then clicking "Copy Table Name". This copies the table name to your computer's clipboard. Then you can paste the name in the SQL query editor using the method you normally use to paste items from the clipboard.

  1. Click Run Query.
  • If the query is valid, the results display in the Results section.
1185

Successful Query

  • If there is a issue with the query, an error display in the Results section. Review the error message to fix the query, and then click Run Query again.
1185

Query Error

📘

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:

  1. From the SQL Query page, enter your query in the SQL query editor text box. For syntax assistance, click the SQL syntax documentation link above the text box.
  2. Click Run Query.
  3. To export a successful query's results to a CSV file format for easier data analysis (for example, to a spreadsheet), click the small export icon above the Results table to download and export results.
1205

Export Results to CSV

Once the results have been downloaded, you can open the file in your text reader or import it in your favorite spreadsheet application.

📘

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.