SQL queries can be used to fetch the data from any of the published Datasets like CSV Datasets, Jiffy Tables, Doc tables and DB connection that is established using the DB connection option.

Create SQL Dataset

To add a SQL Dataset, do the following:

1. Click on the + icon on the Datasets listing page and choose the SQL Datasets option.
2. An SQL Editor screen opens which has all the existing Datasets listed on the left side of the screen and a Query Editor on the right side of the screen with a default query.
3. Click on the required Dataset.
4. A default query with Select * from ‘dataset name/path’ will be provided in the Query Editor which can be edited as required.

   The SQL Query works on Apache Drill. So the queries must be scripted accordingly.

Executing the SQL Query

Click on the Run button (1) in the Query Editor screen to execute the query. Based on this query, the data from the selected dataset will be displayed in a tabular format (2).

SQL Editor Options

The SQL Editor has the following options for manipulating the data and create a new SQL dataset that gets published in the Dataset listing screen.

• Toggle: Clicking on this option (3), the Query Editor view can be toggled on and off.
• Schema Edit: Clicking on this option (4), a window opens with all the columns of the selected dataset. Options to hide, make it nullable along with a More icon having options to duplicate the column, add expression, and delete a column are available for each column. Read more about this [here]().
• Publish: Once the required data is achieved as per the requirements and the Publish icon (5) is clicked to save the Dataset. The data is saved as a new SQL Dataset. This newly created SQL dataset with the manipulated data will get published under the Dataset listing screen.
• Create Linked Table: (6)
• Appendable: (7)

View SQL Dataset

The newly created SQL Datasets will be listed in the Datasets listing page. To view the SQL Datasets schema with the details of the columns, click on the name of the SQL Datasets in the Datasets listing screen.

1. TableFilter:

   • The data in the column can be filtered as required using the Filter option.
   • Clicking on this option, a popup window opens to define the filter conditions. Based on these conditions, the data gets populated in the SQL dataset.
   • Do the following to use the filters:
     • Choose the column name in the first drop-down.
     • Choose a condition to filter in the second drop-down. The following conditions are available:
       • in: To display the values that exist within the specified range.
       • nin: To display the values that do not exist within the specified range.
       • eq: To display the values that are equal to the specified value.
       • ne: To display the values that are not equal to the specified value.
       • empty: To display the values that are empty.
       • nempty: To display the values that are not empty.
       • contains: To display the values that have the specified value as a part of them.
       • ncontains: To display the values that do not have the specified value as a part of them.
       • starts: To display the values that start with the specified value.
       • nstarts: To display the values that do not start with the specified value.
       • ends: To display the values that end with the specified value.
       • nends: To display the values that do not end with the specified value.
     • Click on the APPLY button.

2. Each dataset under the dataset list of the SQL shows a More icon when the cursor hovers on it. 2 options are available in the More menu:

   1. Join: The Join option can be used to combine the data from two tables. When the Join icon is clicked, a Join dialog box opens with the following options:

      • Datasheet1: Name of the first datasheet.
      • Select Column: Column to be selected from the first datasheet.
      • Join Type: Type of join function to be performed. The join type can be inner, left, right or full.
      • Datasheet2: Name of the second datasheet.
      • Select Column: Column to be selected from the second datasheet.

        Multiple tables can be joined using the + icon on the Join dialog box and specifying the name of the datasheet and the column.

        
        

   2. Preview: The Preview option is used to preview the table data.

      Multiple table data can be viewed at the same time using by clicking on this option provided against each dataset.