Using Excel as a Bi Tool for StarRocks on Mac

Microsoft Excel is arguably the most indispensable piece of software in Microsoft's Office Suite, and its extreme flexibility and ease-of-use have made it a workhorse for nearly every area of business analysis. Unfortunately, as analytics operations mature, Excel is often replaced by more powerful tools like Tableau, Power BI, and other business intelligence (BI) offerings.

Investing in these new tools can be costly and can disrupt formally productive users by forcing them to adjust their workflows. In many cases, abandoning Excel may not be necessary, and this tutorial will show you how easy it is to get started using Excel as a BI tool for your analytics with the help of StarRocks.

 

Using Excel as a BI Tool with StarRocks

StarRocks is a next-gen, high-performance analytical database that enables real-time, multi-dimensional, and highly concurrent data analysis. StarRocks is compatible with MySQL protocols and can be easily used with the MySQL client and various BI tools.

You can directly use Excel to send queries and fetch results to and from StarRocks. The retrieved data can then be used for further downstream analysis within Excel. In this tutorial, we will go over how to use Microsoft Excel to connect to StarRocks on MacOS.

 

Pre-Requisites

• StarRocks: V2.5+ recommended for better data type compatibility. Download the latest version here.

• Microsoft Excel: tested version - V16.71 (23031200), other versions should also work.

• MacOS: tested version - 12.2.1 (21D62), other versions should also work.

• CPU architecture: X86 or Apple Silicon

 

Install Dependencies

First, you'll need to install a few dependencies on your Mac:

• ODBC manager

• iODBC

• MySQL ODBC connector

 

How to install ODBC manager

Download link: http://www.odbcmanager.net/

 

 

How to install iODBC

Download link: https://www.iodbc.org/dataspace/doc/iodbc/wiki/iodbcWiki/Downloads

Choose the appropriate version and set up the iODBC.

 

 

How to install the MySQL ODBC connector and configure DSN

Download link: https://dev.mysql.com/downloads/connector/odbc/

 

Configurations

Set up DSN in ODBC Manager

Step 1: Open ODBC Manager.

 

 

Step 2: In System DSN , choose myodbc and click on Configure

 

 

Step 3: Edit the corresponding values to the correct figure.

 

 

Server: server address of StarRocks FE, make sure the network is accessible. If the IP is localhost, you need to write 127.0.0.1 for compatibility.

Port: query_port of StarRocks FE, the default port is 9030

Database (Optional): database you wish to query from.

 

Step 4: Copy related files

Now we need to copy some files. Depending on the ODBC version you are using, you need to:

• ODBC 5: Please copy ibmyodbc5a.so, libmyodbc5w.so (under

/usr/local/mysql-connector-odbc-5.x.xx-macosxx-arm64/lib ) to /Library/ODBC/

• ODBC 8 (required for Apple Scilicon): Please copy libcrypto.1.1.dylib, libmyodbc8a.so, libmyodbc8w.so, libssl.1.1.dylib (under /usr/local/mysql-connector-odbc-8.x.xx-macosxx-arm64/lib) to /Library/ODBC/

Step 5: Modify the /Library/ODBC/odbc.ini

Depending on the ODBC version you are using, you need to:

• If you use ODBC 5, modify the Driver under [myodbc]to /Library/ODBC/libmyodbc5w.so

• If you use ODBC 8, modify the Driver under [myodbc]to /Library/ODBC/libmyodbc8w.so

 

ODBC 5

 

ODBC 8

 

Note: every time you modify the DSN, please remember to check the Driver in /Library/ODBC/odbc.ini .

 

Test DSN in iODBC Administrator

Step 1: Open iODBC Administrator64 (x86) or iODBC Administrator (Apple Scilicon)

 

 

Step 2: In System DSN, choose the myodbc

 

 

Step 3: Click Test, Enter the username and password of StarRocks.

 

 

If the DSN was tested successfully. It's ready to use in Excel.

 

 

Using Excel To Connect to StarRocks

Step 1: Open Excel, choose Data->New Database Query->From Database

 

 

Step 2: Choose the DSN you set up earlier, myodbc, and click Test, enter your username and password for StarRocks.

 

 

 

This indicates the connection is successful. Then you can click ok to enter your username and password and make the connection.

 

Step 3: Then you can enter SQLs in Microsoft Query to query StarRocks.

Now try to run some queries and export to excel:

 

 

Click Return Data to load data in Excel for further analysis.

 

 

You’re Now Ready To Start Using Excel as a Bi Tool

Following this tutorial, you're now ready to use Excel to connect to StarRocks and retrieve query results. Download StarRocks now to get started. And if you've found this guide useful and would like to discover more ways StarRocks can help you deliver fast, fresh, and flexible analytics, check out the StarRocks blog for more ideas.