Run and manage query

Available in VPC

This section describes how to enter and run queries and how to save and utilize the queries you enter.

Simulate query

When you start a query simulation, the query statement you enter is not formally processed; rather, the amount of data scanned when processing that query statement is measured and displayed in the result window. You can predict how much you will be charged by measuring the amount of data to be scanned.
To simulate a query:

1. In the VPC environment of the NAVER Cloud Platform console, navigate to > Services > Big Data & Analytics > Data Query.
2. Click Query Editor.
3. Select a data source and enter a query statement in the query input window.
   • If you are using a template query, you do not have to select a data source as template queries have their data source defined in the statements. Go to the [Query] tab, click [Template query], and select the desired query statement to have it entered automatically.
4. Click [Simulate].
5. Check the results of the simulation in the lower section of the page.

Run query

To run a query:

1. In the VPC environment of the NAVER Cloud Platform console, navigate to > Services > Big Data & Analytics > Data Query.
2. Click Query Editor.
3. Select a data source and enter a query statement in the query input window.
   • Select the database name from the Database dropdown menu to enter the table name easily without having to enter the full source path.
     
   • For template queries, their data source is already defined as a database belonging to "public_data." Go to the [Query] tab, click [Template query], and select the desired ready-to-run query to have it entered automatically.
   • If you are re-running the same query, consider enabling the result reuse feature for efficient processing. For more information, see Enable query result reuse.
4. Click [Run].
   • If you want to measure the amount of data scanned during the query's processing in advance, click [Simulate] to check the measurement result before clicking [Run].
5. Check the query result in the lower section of the page.
   • You can view a variety of information in addition to the query result in the result area. For more information on viewing results, see View query results.
   • The query statement you enter disappears when you leave the page. Save the query statement if you need to. For more information on saving query statements, see Save query statement.

Iceberg table

You can execute a query on an Iceberg table. Apache Iceberg is a high-performance table format for analyzing a vast amount of data. The Apache Iceberg table versions 1 and 2 are supported.

To Create Iceberg table, use the CREATE TABLE USING ICEBERG statement described on the relevant page.

CREATE TABLE TABLENAME (col_name data_type) USING ICEBERG

The supported tasks for Iceberg tables are as follows:

• SELECT, DESCRIBE, SHOW STATS, SHOW CREATE TABLE
• INSERT, UPDATE, MERGE, DELETE
• ALTER TABLE, DROP TABLE, COMMENT

CREATE TABLE TABLENAME (col_name data_type)
with (
  "object_store_layout_enabled" = true, 
  "data_location" = 'data save path', 
  extra_properties = MAP(ARRAY['gc.enabled'], ARRAY['false'])
) 
USING ICEBERG;

Enable query result reuse

Query result reuse refers to the reuse of results from queries processed a certain amount of time ago. You can use it to save query processing time when running queries with the same content repeatedly.
Result reuse must be enabled before running the completed query statement. To enable result reuse:

1. In the VPC environment of the NAVER Cloud Platform console, navigate to > Services > Big Data & Analytics > Data Query.
2. Click Query Editor.
3. Enter the query statement in the query input window.
4. Find the Reuse results item in the upper right corner of the query window and click Do not reuse.
   
5. Find the dropdown menu and select the time frame you want to allow query result reuse.
   • You can select from 1 hour ago, 12 hours ago, or 1 day ago. Alternately, click Enter manually and select a time between 1 minute and 7 days.
   • Click [Run] to execute the query after completing the configuration. If a valid relevant result exists, it will be reused.

Execute query using parameter

You compose a query including variables and bind the variables to the execution time for dynamic query execution. The following is a query execution example using parameters:

• Query text to execute

  select * 
  from public_data.incheon_airport.cargo_flight_schedule_summer_arrival
  where TRY_CAST(year AS INTEGER) >= ?
    and TRY_CAST(year AS INTEGER) <  ?
    and airline = ?
  

• Parameters
  • Parameter 1: 2015
  • Parameter 2: YEAR(NOW())
  • Parameter 3: KOREAN AIR

Storage catalog

You can enter the path of the file stored in Object Storage to run the query directly without creating a table.
The following is an example of running a query on the file stored in Object Storage using the storage catalog:

• Use the TABLE(object_storage.file.read('TYPE', 'PATH')) syntax in the table location.
  • TYPE: File type (supported formats: csv, tsv, json, avro, parquet, and orc)
  • PATH: The path of the file stored in Object Storage

select * from TABLE(object_storage.file.read('csv', 's3a://test-bucket/path/file.csv')) ;

Provided functions

The following is the list of functions provided by Data Query:

Manage query

You can save your query statement as My query so that it can be reused. Once saved, the query statement can be edited, copied, or deleted as needed. You can also run the saved query statement at the desired time by using the scheduled execution feature.

Save query statements

You can save a manually written query statement or template-based query statement as My query. To save query statements:

1. In the VPC environment of the NAVER Cloud Platform console, navigate to > Services > Big Data & Analytics > Data Query.
2. Click Query Editor.
3. Enter the query statement in the query input window.
   • If you want to use a template query, go to the [Query] tab, click [Template query], and select the desired query statement to have it entered automatically.
4. Hover the cursor over the options menu on the right side of the query tab in the upper section of the query window.
   
5. Click Save or Save as.
6. Enter a name for the query and click [Save] in the popup window.
   • The query is saved to My query list. Click the [Query] tab > [My query] on the left side of the page to see the list of your saved queries.
     

Edit saved query

You can edit a saved query's name or content, save it under a different name, or delete it. To edit a saved query:

1. In the VPC environment of the NAVER Cloud Platform console, navigate to > Services > Big Data & Analytics > Data Query.
2. Click Query Editor.
3. Click the [Query] tab > [My query] on the left side of the page.
4. Click the query you want to edit in My query list.
   • That prompts the selected query's content to be entered in the query window.
5. Hover the cursor over the options menu on the right side of the query tab in the upper section of the query window.
   
6. Select and click the desired menu.
   • Edit name: Click to update the saved query's name. If you edit the query name while you have edited the content in the query window, the content is updated too.
   • Save: Run the edited statement in the query window to overwrite the saved query.
   • Save as: Copy the query's content and save it under a different name.
   • Delete: Delete the query from My query list.

Schedule saved query's execution

You can run a saved query at the desired time by adding an execution schedule to the saved query statement. To set an execution schedule:

1. In the VPC environment of the NAVER Cloud Platform console, navigate to > Services > Big Data & Analytics > Data Query.
2. Click Query Editor.
3. Click the [Query] tab > [My query] on the left side of the page.
4. Find the query you want to edit in My query list. Hover the cursor over the options menu on the right side of the query.
5. Click Set execution schedule.
   • Set execution schedule: Set execution schedule details so that the saved query statement is executed at the desired time/cycle.
   • Delete execution schedule: Delete the set execution schedule information.