Local Client Dbeaver

1. Dbeaver Local Client Mysql
2. Local Client Postgresql Dbeaver
3. Dbeaver Local Client Docker

Well, analyzing the spread of the SARS-CoV-2 coronavirus was not my dream use case. But based on the responses to Ferry Djaja‘s Tracking Coronavirus COVID-19 Near Real Time with SAP HANA XSA article I decided to add my two groszy too.

[Updated on 20-03-30 with the changed links to the source data; and the new map output based on the new data granularity. Thanks Douglas Maltby for your comment!]

Hi Wilhem, I have also been evaluating Ignite, and also followed the steps in setting up SQL tooling with DBeaver. At that final step, when you choose a driver class - I had a choice (from a pulldown list, I think) between the two classes. Academic License. This article shows how to connect to Oracle data with wizards in DBeaver and browse data in the DBeaver GUI. Create a JDBC Data Source for Oracle Data. Follow the steps below to load the driver JAR in DBeaver. Open the DBeaver application and, in the Databases menu, select the Driver Manager option. Click New to open the Create New Driver form.

In his blog post, Ferry used JavaScript in SAP HANA XSA to pull the data from CSV files updated daily by Johns Hopkins University.

I would like to show you how you can pull and load these files into SAP HANA using just a few lines of code thanks to SAP HANA Python Client API for Machine Learning (hana_ml package).

Dbeaver Local Client Mysql

Some people were confused with the visualization on the map at the end — please note that this article focuses on technical use case connecting different components, not on doing coronavirus data deep analysis.

Get Python environment, e.g. Jupyter

Local Client Postgresql Dbeaver

I will use Jupyter in the Docker container for that. Please have a look at my previous post Understanding containers (part 05): shared files between the host and containers if you are not familiar with how to start it. As well you can do all the same steps below from any other Python environment.

So, I have my container myjupyter01 running. I am connected to the Jupyter UI as described in the previous blog.

Install hana_ml

The Jupyter image I used from the Docker Hub registry was jupyter/minimal-notebook. It contains already some popular data processing packages, like pandas.

But additionally, I need to install hana_ml, which — in its current version 1.0.8 — is available on PyPI repository: https://pypi.org/project/hana-ml/.

The command to run the installation is python -m pip install hana_ml, but because I am running it from Jupyter notebook with Python3 kernel, I need to run it with ! at the beginning:

Dbeaver Local Client Docker

Obviously, this installation step has to be done only once. No need to rerun it in the same container e.g. when reloading the newest files.

Use pandas to import files with data

Let’s import the same three files (confirmed, deaths, recovered) from https://github.com/CSSEGISandData/COVID-19/tree/master/csse_covid_19_data/csse_covid_19_time_series as Ferry used in his example.

As you can see from the preview of the Pandas dataframe, it lists only countries or provinces with confirmed cases, and every day the new column is added with the latest data from the previous day. Lines are added when the first case(s) is confirmed in the new region.

Use pandas to re-format the data frame

Before persisting the data in SAP HANA, let’s:

1. Remove all date columns except the last one,
2. Rename the last column from the actual date (like today’s 3/10/20 to Confirmed).

Use hana_ml to persist data in SAP HANA table

Now let me connect to my instance of SAP HANA Express with the user hanaml that already exists there…

…and convert the Pandas dataframe df_confd_latest into a HANA dataframe hdf_confd.

Once the HANA dataframe is created:

1. A physical column table is created in HANA and data from Pandas dataframe is inserted there,
2. HANA dataframe hdf_confd in Python does not store any data in your laptop, but only points to a table HANAML.df_confd in SAP HANA server memory, and all Python operations on the HANA dataframe are physically exected in HANA db without moving data between the server and a client,
3. To display the result of any operations, we need to apply collect() method to convert HANA dataframe to Pandas (and as a result to bring data from HANA db server to the local client).

Use DBeaver to check data in SAP HANA…

You might remember me already using DBeaver — the free database tool supporting SAP HANA — in my previous post “GeoArt with SAP HANA and DBeaver“.

I am using it now again, and indeed I can find the table df_confd in the schema HANAML with all the data from the source Pandas dataframe.

…and do a spatial preview

As the table contains latitude and longitude columns I can visualize impacted countries/states right from DBeaver with the following SQL using Spatial data preview.

I needed to change the map projection to EPSG:4326 to get these points on the map. And DBeaver shows me the rest of the record data when I click on any point.

[Below is the old screenshot from 2020-03-11, which demonstrates as well the different granularity of e.g. US data used at that time]

DBeaver spatial preview is not a full-blown geospatial visual exploration tool. Yet it is good enough to see impacted countries/regions (depending on the granularity in the source files).

Should you be interested to learn more about hana_ml…

… then I would definitely recommend checking Hands-On Tutorial: Machine Learning push-down to SAP HANA with Python by Andreas Forster.

HANA ML is a part of the new “Advanced Analytics with SAP HANA” topic for CodeJam events. Unfortunately because of the coronavirus situation, we had to cancel the first one organized by Jakob Flaman in Bern this month. Another one is organized by Ewelina Pękała on May 27th in Katowice: https://www.eventbrite.com/e/sap-codejam-katowice-registration-99016299417. Hopefully, the situation gets normal by that time, and we will not need to cancel this one too.

Stay healthy ❤️
-Vitaliy (aka @Sygyzmundovych)

DBeaver Overview

DBeaver is a free, open source multiplatform database management tool and SQL client for developers and database administrators. DBeaver can be used to access any database or cloud application that has an ODBC or JDBC driver, such as Oracle, SQL Server, MySQl, Salesforce, or MailChimp. Devart DBeaver provides you with the most important features you'd need when working with a database in a GUI tool, such as:

• SQL queries execution
• Metadata browsing and editing
• SQL scripts management
• Data export/import
• Data backup
• DDL generation
• ER diagrams rendering
• Test data generation
• BLOB/CLOB support
• Database objects browsing
• Scrollable resultsets

The tool comes in two editions — Community and Enterprise. Enterprise Edition supports NoSQL databases, such as MongoDB or Cassandra, persistent query manager database, SSH tunneling, vector graphics (SVG) and a few other enterprise-level features. Note though that you can access a MongoDB database from DBeaver Community Edition using the respective Devart ODBC driver. For the purposes of this guide, we'll use the Community Edition of DBeaver to retrieve data from Oracle via the Open Database Connectivity driver.

Creating an ODBC Data Source to Use Oracle Data in DBeaver

1. Click the Start menu and select Control Panel.
2. Select Administrative Tools, then click ODBC Data Sources.
3. Click on the System DSN tab if you want to set up a DSN name for all users of the system or select User DSN to configure DSN only for your account.
4. Click the Add button and double-click Devart ODBC Driver for Oracle in the list.
5. Give a name to your data source and set up the connection parameters.
6. Click the Test Connection button to verify that you have properly configured the DSN.

When using ODBC driver for Oracle with DBeaver, SQL_WVARCHAR data types may be displayed incorrectly in DBeaver. To prevent this, you need to set the string data types to Ansi either in the Advanced Settings tab of the driver configuration dialog or directly in the connection string (String Types=Ansi) — all string types will be returned as SQL_CHAR, SQL_VARCHAR and SQL_LONGVARCHAR.

Connecting to Oracle Data from DBeaver via ODBC Driver for Oracle

Follow the steps below to establish a connection to Oracle in DBeaver.

1. In the Database menu, select New Database Connection.
2. In the Connect to database wizard, select ODBC and click Next.
3. Enter the previously configured DSN in the Database/Schema field.
4. Click Test Connection. If everything goes well, you'll see the Success message.

Viewing Oracle Database Objects and Querying Data

You can expand out the database structure in DBeaver's Database Navigator to visualize all the tables in Oracle database. To view and edit the data in a table, you need to right-click on the target table name and select View data.The content of the table will be displayed in the main workspace.

If you want to write a custom SQL query that will include only the necessary columns from the table, you can select New SQL Editor in the SQL Editor main menu. Create your query and run it by clicking Execute SQL Statement to view the results in the same window.