ODBC client applicationsedit

Warning

This functionality is experimental and may be changed or removed completely in a future release. Elastic will take a best effort approach to fix any issues, but experimental features are not subject to the support SLA of official GA features.

Thanks to a standardized API, a broad range of third-party ODBC-enabled applications can access Elasticsearch using Elasticsearch SQL ODBC Driver. This section will collect alphabetically an increasing list of such applications along with the configuration steps that need to be taken within them.

Note

Each application has its own requirements and licensing; these are outside the scope of this documentation which only covers the configuration aspect of integration with Elasticsearch SQL.

Warning

The support for applications implementing the ODBC 2.x standard and prior is currently limited.

Microsoft Exceledit

Warning

This functionality is experimental and may be changed or removed completely in a future release. Elastic will take a best effort approach to fix any issues, but experimental features are not subject to the support SLA of official GA features.

 

Microsoft Excel is a software program […] that allows users to organize, format and calculate data with formulas using a spreadsheet system.

 
 -- https://www.techopedia.com/definition/5430/microsoft-excel

Prerequisitesedit

  • Microsoft Office 2016 or higher
  • Elasticsearch SQL ODBC Driver
  • A preconfigured User or System DSN (see Configuration section on how to configure a DSN).

Load data into a spreadsheetedit

First, you’ll need to choose ODBC as the source to load data from. To do so, click on the Data tab, then New Query button, in the drop-down menu expand From Other Sources, then choose From ODBC:

ODBC as data source. images/apps_excel_fromodbc.png

This will open a new window with a drop down menu populated with the DSNs that Excel found on the system. Choose a DSN configured to connect to your Elasticsearch instance and press the OK button:

Choose a DSN. images/apps_excel_dsn.png

This will lead to a new window, allowing the user to input the connection credentials.

A username might be required by Excel even if the Elasticsearch instance has no security enabled. Providing a bogus username with no password in this case will not hinder the connectivity. Note however that Excel will cache these credentials (so in case you do have security enabled, you won’t be prompted for the credentials a second time).

Fill in the username and the password and press Connect.

Provide connection credentials. images/apps_excel_cred.png

Once connected, Excel will read Elasticsearch’s catalog and offer the user a choice of tables (indices) to load data from. Clicking on one of the tables will load a preview of the data within:

Pick table to load. images/apps_excel_picktable.png

Now click the Load button, which will have Excel load all the data from the table into a spreadsheet:

Data loaded in spreadsheet. images/apps_excel_loaded.png

Microsoft Power BI Desktopedit

Warning

This functionality is experimental and may be changed or removed completely in a future release. Elastic will take a best effort approach to fix any issues, but experimental features are not subject to the support SLA of official GA features.

 

Power BI is a business analytics solution that lets you visualize your data and share insights across your organization, or embed them in your app or website.

 
 -- https://powerbi.microsoft.com/en-us/what-is-power-bi/

Prerequisitesedit

  • Microsoft Power BI Desktop 2.63 or higher
  • Elasticsearch SQL ODBC Driver
  • A preconfigured User or System DSN (see Configuration section on how to configure a DSN).

Data loadingedit

First, you’ll need to choose ODBC as the source to load data from. Once launched, click on the Get Data button (under Home tab), then on the More… button at the bottom of the list:

Get Data / More…. images/apps_pbi_fromodbc1.png

In the new opened window scroll at the bottom of the All list and select the ODBC entry, then click on the Connect button:

ODBC / Connect. images/apps_pbi_fromodbc2.png

This will replace current window with a new From ODBC one, where you’ll have to select a previously configured DSN:

Choose a DSN. images/apps_pbi_dsn.png

Once connected Power BI will read Elasticsearch’s catalog and offer the user a choice of tables (indices) to load data from. Clicking on one of the tables will load a preview of the data within:

Pick table to load. images/apps_pbi_picktable.png

Now tick the chosen table and click on the Load button. Power BI will now load and anlyze the data, populating a list with the available columns. These can now be used to build the desired visualisation:

Visualize the data. images/apps_pbi_loaded.png

Microsoft PowerShelledit

Warning

This functionality is experimental and may be changed or removed completely in a future release. Elastic will take a best effort approach to fix any issues, but experimental features are not subject to the support SLA of official GA features.

 

PowerShell is a task-based command-line shell and scripting language built on .NET.

 
 -- https://docs.microsoft.com/en-us/powershell/scripting/powershell-scripting

PowerShell is available on all recent Windows Desktop OSes. It also has embedded ODBC support, thus offering a quick and accessible way to connect to Elasticsearch.

Prerequisitesedit

  • Microsoft PowerShell
  • Elasticsearch SQL ODBC Driver
  • A preconfigured User or System DSN (see Configuration section on how to configure a DSN).

Writing a scriptedit

While putting the following instructions into a script file is not an absolute requirement, doing so will make it easier to extend and reuse. The following instructions exemplify how to execute a simple SELECT query from an existing index in your Elasticsearch instance, using a DSN configured in advance. Open a new file, select.ps1, and place the following instructions in it:

$connectstring = "DSN=Local Elasticsearch;"
$sql = "SELECT * FROM library"

$conn = New-Object System.Data.Odbc.OdbcConnection($connectstring)
$conn.open()
$cmd = New-Object system.Data.Odbc.OdbcCommand($sql,$conn)
$da = New-Object system.Data.Odbc.OdbcDataAdapter($cmd)
$dt = New-Object system.Data.datatable
$null = $da.fill($dt)
$conn.close()
$dt

Now open a PowerShell shell and simply execute the script:

Run SQL in PowerShell. images/apps_ps_exed.png

MicroStrategy Desktopedit

Warning

This functionality is experimental and may be changed or removed completely in a future release. Elastic will take a best effort approach to fix any issues, but experimental features are not subject to the support SLA of official GA features.

 

MicroStrategy Desktop is a free data discovery tool that helps people bring data to life using powerful self-service analytics.

 
 -- https://www.microstrategy.com/us/resources/library/videos/new-microstrategy-desktop

Prerequisitesedit

  • MicroStrategy Desktop 11 or higher
  • Elasticsearch SQL ODBC Driver
  • A preconfigured User or System DSN (see Configuration section on how to configure a DSN).

Data loadingedit

To use the Elasticsearch SQL ODBC Driver to load data into MicroStrategy Desktop perform the following steps in sequence.

  1. Create a New Dossier

    Once the application is launched, you’ll first need to create a New Dossier:

    images/apps_microstrat_newdossier.png

  2. New Data

    To import into the New Dossier just opened, press on the New Data button in the DATASETS column:

    images/apps_microstrat_newdata.png

  3. Access data from Tables

    This opens a new window that allows to choose the source to load data from. Click on the Databases icon:

    images/apps_microstrat_databases.png

  4. New Data Source

    In the newly opened Import from Table - Select window, click on the + button to the right of DATA SOURCES item:

    images/apps_microstrat_newds.png

  5. Data Source

    In the Data Source window, tick the radio button for DSN Data Sources. In the DSN drop-down box, choose the name of the DSN that you have previously configured. For the Version, chose Generic DBMS.

    Input a user name and password in the provided fields. Note that the application requires them irrespective of the fact that they might already be part of the previously configured DSN and the new input will take precedence over those.

    Finally, give a name to the application-specific data source you’re just configuring:

    images/apps_microstrat_dsn.png

  6. Select Import Options

    Choosing an import methodology follows. You can pick any of the options; we’ll exemplify the Select Tables option:

    images/apps_microstrat_tables.png

  7. Import from Table - Select

    The data source you’ve named two steps before is now listed in the DATA SOURCES column. Clicking on its name triggers the application to query the Elasticsearch instance configured in the DSN and list the tables available within:

    images/apps_microstrat_loadtable.png

  8. Data Access Mode

    Choose a table to load data from and press the Finish button. When doing so, the application offers to choose a loading methdology. You can choose whichever, we’ll exemplify the Connect Live way:

    images/apps_microstrat_live.png

  9. Visualize the data

    From the DATASETS column you can choose what table columns (or index fields) to visualize:

    images/apps_microstrat_visualize.png

Qlik Sense Desktopedit

Warning

This functionality is experimental and may be changed or removed completely in a future release. Elastic will take a best effort approach to fix any issues, but experimental features are not subject to the support SLA of official GA features.

 

Qlik Sense Desktop is a Windows application that gives individuals the opportunity to use Qlik Sense and create personalized, interactive data visualizations, reports, and dashboards from multiple data sources with drag-and-drop ease.

 
 -- https://help.qlik.com/en-US/sense/February2018/Subsystems/Hub/Content/Introduction/at-a-glance.htm

Prerequisitesedit

  • Qlik Sense Desktop November 2018 or higher
  • Elasticsearch SQL ODBC Driver
  • A preconfigured User or System DSN (see Configuration section on how to configure a DSN).

Data loadingedit

To use the Elasticsearch SQL ODBC Driver to load data into Qlik Sense Desktop perform the following steps in sequence.

  1. Create new app

    Once the application is launched, you’ll first need to click on the Create new app button:

    images/apps_qlik_newapp.png

  2. Name app

    …then give it a name,

    images/apps_qlik_create.png

  3. Open app

    …and then open it:

    images/apps_qlik_open.png

  4. Add data to your app

    Start configuring the source to load data from in the newly created app:

    images/apps_qlik_adddata.png

  5. Load from ODBC

    You’ll be given a choice of sources to select. Click on the ODBC icon:

    images/apps_qlik_odbc.png

  6. Choose DSN

    In the Create new connection (ODBC) dialog, click on the DSN name that you have previously configured for your Elasticsearch instance:

    images/apps_qlik_dsn.png

    Provide a username and password in the respective fields, if authentication is enabled on your instance and if these are not already part of the DSN. Press the Create button.

  7. Select source table

    The application will now connect to the Elasticsearch instance and query the catalog information, presenting you with a list of tables that you can load data from:

    images/apps_qlik_selecttable.png

  8. Visualize the data

    Press on the Add data button and customize your data visualization:

    images/apps_qlik_visualize.png

Tableau Desktopedit

Warning

This functionality is experimental and may be changed or removed completely in a future release. Elastic will take a best effort approach to fix any issues, but experimental features are not subject to the support SLA of official GA features.

 

Tableau is the most powerful, secure, and flexible end-to-end analytics platform for your data.

 
 -- https://www.tableau.com/products/what-is-tableau

Prerequisitesedit

  • Tableau 2018 or higher
  • Elasticsearch SQL ODBC Driver
  • A preconfigured User or System DSN (see Configuration section on how to configure a DSN).

Data loadingedit

First, you’ll need to choose ODBC as the source to load data from. Once launched, click on the More… menu item and in the expanded list of connectors, choose Other Databases (ODBC):

ODBC as data source. images/apps_tableau_fromodbc.png

In the new connection window that appears, select the previously configured DSN that will connect to the desired Elasticsearch instance. Press the Connect button. In case credentials are needed, a new windows - driver’s DSN editor - will be launched and you’ll need to provide the Username and Password in the respective fields. (Note that these will not be stored as part of the DSN, but only remembered for the duration of the session). If the connection is successful, the Connection Attributes section of the connection window is populated with the respective details; press the Sign In button next:

Authenticate and sign in. images/apps_tableau_connd.png

In Tableau Desktop’s main window then choose the name of Elasticsearch instance as the Database, select one table that you’d like to load from the list (click on the magnifying glass icon to have them all shown), drag the table over the work area, then click the Update Now button to load a preview of the table:

Data loaded. images/apps_tableau_loaded.png