Using Drill Explorer

Drill demonstrates the unique ability to query self-describing data in file systems and NoSQL databases like HBase directly without defining metadata definitions in Hive. Therefore, users need a mechanism for exploring the metadata in these environments using SQL queries and for creating views for visualization using BI tools. Drill Explorer is a standalone application designed for use alongside your Business Intelligence application as part of the ODBC DSN experience, allowing you to do the following:

• Browse data in your Drill data source
• Define and save SQL views

When retrieving data from your Drill data store, you can use the SQL views created through Drill Explorer to select the data to include.

For information about the schemas and file formats that the Simba Apache Drill ODBC Connector supports, see Schema Types and File Formats.

To run Drill Explorer:

1. Choose one:
   • Either click Applications, then click Other, and then click Drill Explorer.
   • Or navigate to the opt/simba/drill/DrillExplorer directory and run DrillExplorer.
2. In Drill Explorer, click Connect.
3. Choose one:
   • If you are connecting through a DSN, then click the ODBC DSN tab, select your DSN, and then click Connect.
   • Or, if you are using a DSN-less connection, then click the Advanced tab, type your connection string in the field, and then click Connect. For information about DSN-less connection strings, see Using a Connection String.
4. If your connection requires authentication, type your user name and password.
5. Click OK.
6. To disconnect from a DSN, select the DSN in the Schemas pane and then click Disconnect

To browse data in your Drill data source:

1. In Drill Explorer, in the Schemas pane, click to expand the schema that you want to browse.
2. Continue expanding branches in the schema as needed, and then select the table or file you want to view.

   When you select an item in the Schemas pane, the Browse tab displays and the SQL tab becomes available. In the Browse tab, the Metadata pane displays the structure of the selected table or file. The Preview pane displays the data that the selected table or file contains. To see the SQL statement that returns the data currently appearing in the Browse tab, click the SQL tab.

3. To specify the maximum number of rows that can appear in the Preview pane, type a value in the field at the bottom of the Preview pane, and then click Sample.
4. To update the data in Drill Explorer after changes have been made in the data source, in the Schemas pane, right-click the item that you want to update, and then click Refresh.

   Important: The Refresh command does not update everything that appears in the Schemas pane. The scope of the Refresh command depends on the item that you right-click to access the command:
   - If you right-click a table, view, or file, then Drill Explorer updates everything that is in the same schema or directory as the selected table, view, or file.
   - If you right-click a schema or directory, then Drill Explorer updates everything in the selected schema or directory. Other schemas or directories on the same hierarchy level are not affected.

To define and save a SQL view:

1. In Drill Explorer, select an item in the Schemas pane, and then click the SQL tab. The SQL tab displays the SQL statement that returns the selected data.
2. In the View Definition SQL field, type a SQL statement defining the desired view or modify the SQL statement appearing in the field as needed, and then click Preview to test the SQL statement.
3. To save the view, click Create As. In the Create As dialog box, in the Schema list, select the schema where you want to save the view.
4. In the View Name field, type a descriptive name.
   Important:

   Do not include spaces in the view name.

5. If you are saving over an existing view, select the Overwrite Existing Views check box.
6. Click Save. A message appears in the dialog box indicating whether the view saved successfully. To copy the message, click Copy.
7. Close the dialog box.