2. Getting Started With Kyuubi and DBeaver - 图1

Getting Started With Kyuubi and DBeaver

What is DBeaver

DBeaver is a free multi-platform database tool for developers, database administrators, analysts and all people who need to work with databases. Supports all popular databases as well as our Kyuubi.

Get to know more About DBeaver.

Preparation

Get DBeaver and Install

Please go to Download DBeaver page to get and install an appropriate release version for yourself.

Get Kyuubi Started

Get the server Started first before your try DBeaver with Kyuubi.

  1. Welcome to
  2. __ __ __
  3. /\ \/\ \ /\ \ __
  4. \ \ \/'/' __ __ __ __ __ __\ \ \____/\_\
  5. \ \ , < /\ \/\ \/\ \/\ \/\ \/\ \\ \ '__`\/\ \
  6. \ \ \\`\\ \ \_\ \ \ \_\ \ \ \_\ \\ \ \L\ \ \ \
  7. \ \_\ \_\/`____ \ \____/\ \____/ \ \_,__/\ \_\
  8. \/_/\/_/`/___/> \/___/ \/___/ \/___/ \/_/
  9. /\___/
  10. \/__/

For debugging purpose, you can tail -f to track the server log like:

  1. $ tail -f /Users/kentyao/Downloads/kyuubi/kyuubi-1.3.0-incubating-bin/logs/kyuubi-kentyao-org.apache.kyuubi.server.KyuubiServer-hulk.local.out
  2. 2021-01-16 03:27:35.449 INFO server.NIOServerCnxnFactory: Accepted socket connection from /127.0.0.1:65320
  3. 2021-01-16 03:27:35.453 INFO server.ZooKeeperServer: Client attempting to establish new session at /127.0.0.1:65320
  4. 2021-01-16 03:27:35.455 INFO persistence.FileTxnLog: Creating new log file: log.1
  5. 2021-01-16 03:27:35.491 INFO server.ZooKeeperServer: Established session 0x177078469840000 with negotiated timeout 60000 for client /127.0.0.1:65320
  6. 2021-01-16 03:27:35.492 INFO zookeeper.ClientCnxn: Session establishment complete on server 127.0.0.1/127.0.0.1:2181, sessionid = 0x177078469840000, negotiated timeout = 60000
  7. 2021-01-16 03:27:35.494 INFO state.ConnectionStateManager: State change: CONNECTED
  8. 2021-01-16 03:27:35.495 INFO client.ServiceDiscovery: Zookeeper client connection state changed to: CONNECTED
  9. 2021-01-16 03:27:36.516 INFO client.ServiceDiscovery: Created a /kyuubi/serviceUri=localhost:10009;version=1.0.2;sequence=0000000000 on ZooKeeper for KyuubiServer uri: localhost:10009
  10. 2021-01-16 03:27:36.516 INFO client.ServiceDiscovery: Service[ServiceDiscovery] is started.
  11. 2021-01-16 03:27:36.516 INFO server.KyuubiServer: Service[KyuubiServer] is started.

Configurations

Start DBeaver

If you have successfully installed DBeaver, just hit the button to launch it.

Select a database

Substantially, this step is to choose a JDBC Driver type to use later. We can choose Apache Hive or Apache Spark to set up a driver for Kyuubi, because they are compatible with the same client.

2. Getting Started With Kyuubi and DBeaver - 图2 Tips: zoom up if the pic looks small

Click next…

Edit the Driver

We can set libraries that include the org.apache.hive.jdbc.HiveDriver and all of its dependencies.

2. Getting Started With Kyuubi and DBeaver - 图3 Tips: zoom up if the pic looks small

Download/Update it… or,

2. Getting Started With Kyuubi and DBeaver - 图4 Tips: zoom up if the pic looks small

We can configure it by adding a local folder which contains these jars.

Generic JDBC Connection Settings

To connect to Kyuubi, we should configure the right host and port that starts the server. By default, Kyuubi starts on port 10009 on your localhost.

2. Getting Started With Kyuubi and DBeaver - 图5 Tips: zoom up if the pic looks small

Other settings

We also can name a recognizable title for this connection.

2. Getting Started With Kyuubi and DBeaver - 图6 Tips: zoom up if the pic looks small

Interacting With Kyuubi server

Connections

First, we need to active the connection with Kyuubi server we created in the above steps.

Correspondingly, the server will help us start an engine, and we will be able to see a log like below,

  1. 2021-01-16 14:33:56.050 INFO session.KyuubiSessionImpl: Launching SQL engine:

Once the connection is set up, we shall be able to see the default catalog, databases(namespaces) as below.

2. Getting Started With Kyuubi and DBeaver - 图7

Operations

Now, we can use the SQL editor to write queries to interact with Kyuubi server through the connection.

  1. DESC NAMESPACE DEFAULT;

2. Getting Started With Kyuubi and DBeaver - 图8

  1. CREATE TABLE spark_catalog.`default`.SRC(KEY INT, VALUE STRING) USING PARQUET;
  2. INSERT INTO TABLE spark_catalog.`default`.SRC VALUES (11215016, 'Kent Yao');

2. Getting Started With Kyuubi and DBeaver - 图9 Tips: zoom up if the pic looks small

  1. SELECT KEY % 10 AS ID, SUBSTRING(VALUE, 1, 4) AS NAME FROM spark_catalog.`default`.SRC;

2. Getting Started With Kyuubi and DBeaver - 图10 Tips: zoom up if the pic looks small

  1. DROP TABLE spark_catalog.`default`.SRC;

One more case with TPCDS

After we create the TPCDS table in Kyuubi server side, we are able to get all the database objects, including catalogs, databases, tables, and columns e.t.c.

2. Getting Started With Kyuubi and DBeaver - 图11

Tips: zoom up if the pic looks small

Also, we can use the shortcut key to operating metadata and data, for example.

2. Getting Started With Kyuubi and DBeaver - 图12

Tips: zoom up if the pic looks small

And we can write simple or complex SQL to manipulate data, for example, here is the query 41 generated by TPCDS dsqgen tool.

  1. SELECT DISTINCT (i_product_name)
  2. FROM item i1
  3. WHERE i_manufact_id BETWEEN 738 AND 738 + 40
  4. AND (SELECT count(*) AS item_cnt
  5. FROM item
  6. WHERE (i_manufact = i1.i_manufact AND
  7. ((i_category = 'Women' AND
  8. (i_color = 'powder' OR i_color = 'khaki') AND
  9. (i_units = 'Ounce' OR i_units = 'Oz') AND
  10. (i_size = 'medium' OR i_size = 'extra large')
  11. ) OR
  12. (i_category = 'Women' AND
  13. (i_color = 'brown' OR i_color = 'honeydew') AND
  14. (i_units = 'Bunch' OR i_units = 'Ton') AND
  15. (i_size = 'N/A' OR i_size = 'small')
  16. ) OR
  17. (i_category = 'Men' AND
  18. (i_color = 'floral' OR i_color = 'deep') AND
  19. (i_units = 'N/A' OR i_units = 'Dozen') AND
  20. (i_size = 'petite' OR i_size = 'large')
  21. ) OR
  22. (i_category = 'Men' AND
  23. (i_color = 'light' OR i_color = 'cornflower') AND
  24. (i_units = 'Box' OR i_units = 'Pound') AND
  25. (i_size = 'medium' OR i_size = 'extra large')
  26. ))) OR
  27. (i_manufact = i1.i_manufact AND
  28. ((i_category = 'Women' AND
  29. (i_color = 'midnight' OR i_color = 'snow') AND
  30. (i_units = 'Pallet' OR i_units = 'Gross') AND
  31. (i_size = 'medium' OR i_size = 'extra large')
  32. ) OR
  33. (i_category = 'Women' AND
  34. (i_color = 'cyan' OR i_color = 'papaya') AND
  35. (i_units = 'Cup' OR i_units = 'Dram') AND
  36. (i_size = 'N/A' OR i_size = 'small')
  37. ) OR
  38. (i_category = 'Men' AND
  39. (i_color = 'orange' OR i_color = 'frosted') AND
  40. (i_units = 'Each' OR i_units = 'Tbl') AND
  41. (i_size = 'petite' OR i_size = 'large')
  42. ) OR
  43. (i_category = 'Men' AND
  44. (i_color = 'forest' OR i_color = 'ghost') AND
  45. (i_units = 'Lb' OR i_units = 'Bundle') AND
  46. (i_size = 'medium' OR i_size = 'extra large')
  47. )))) > 0
  48. ORDER BY i_product_name
  49. LIMIT 100

2. Getting Started With Kyuubi and DBeaver - 图13 Tips: zoom up if the pic looks small

Epilogue

There are many other amazing features in both Kyuubi and DBeaver and here is just the tip of the iceberg. The rest is for you to discover.