Writing SQL queries using data in Hive

Applies to: Kyvos Enterprise  Kyvos Cloud (SaaS on AWS) Kyvos AWS Marketplace

Kyvos Azure Marketplace   Kyvos GCP Marketplace Kyvos Single Node Installation (Kyvos SNI)

You can process a semantic model using Spark SQL. To do this, write an SQL query using tables and views created in Hive. You must choose an HCatalog SQL as a source.

You can also design a dataset based on Hive views. You can use only Spark SQL-supported syntax, clauses, and keywords to fetch data from tables and views created in Hive.

Incremental identifier

Use the incremental identifier to specify the column you want to use to identify and process new data during an incremental process. The data should be sorted in this column or a date column. Kyvos stores the last processed column value with the process.

To register a Hive table, perform the following steps. 

1. From the Toolbox, click Datasets.

2. Click the Actions menu ( ⋮ ), click Add Dataset, enter a name, and then click Add.

3. Set input type to SQL.

4. Optionally, you can click the Lookup file checkbox to create a lookup file. Refer to using lookup for details.

5. Enter SQL code into the editor. 
   (See the code example in this topic.)

6. Optionally, to format and filter data, click Refine. 

   1. On the Format tab, use the Incremental Identifier field to specify the column you want to use to identify and process new data during an incremental process.

   2. Use the Preview to confirm the results. 
      

7. Click Apply.

8. Optionally, you can click the Actions menu (...) to validate the data or add a note.

9. Click Save.

SELECT Sum(lo_extendedprice * lo_discount) AS revenue,  
       d_yearmonthnum,  
       lo_discount,  
       lo_quantity  
FROM   pdoot.lineorder_v,  
       pdoot.date_v  
WHERE  lo_orderdate = d_datekey  
       AND d_yearmonthnum = 199401  
       AND lo_discount BETWEEN 4 AND 6  
       AND lo_quantity BETWEEN 20000 AND 520000  
GROUP  BY d_yearmonthnum,  
          lo_discount,  
          lo_quantity  

Example SQL code

To use the SQL editor, perform the following steps. 

1. Start typing in the editor box.

2. As you enter text, suggested statements, functions, operators, and so on, are displayed. Click the item to auto-fill it.

3. Use the Undo and Redo icons as needed as you create your calculated expression. 

4. To find or replace content, click the Find/Replace icon, and enter what to find.

   1. Select Match Case if needed.

   2. If you want to replace the found value, enter the text to replace and specify whether to replace all.

   3. To delete the entry for that item, click the X at the end of the line.

Related topics

• Formatting columns