Query data with Pulsar SQL

Before querying data in Pulsar, you need to install Pulsar and built-in connectors.

Requirements

  1. Install Pulsar.
  2. Install Pulsar built-in connectors.

Query data in Pulsar

To query data in Pulsar with Pulsar SQL, complete the following steps.

  1. Start a Pulsar standalone cluster:
  1. PULSAR_STANDALONE_USE_ZOOKEEPER=1 ./bin/pulsar standalone

Query data - 图1note

Starting the Pulsar standalone cluster from scratch doesn’t enable ZooKeeper by default. However, the Pulsar SQL depends on ZooKeeper. Therefore, you need to set PULSAR_STANDALONE_USE_ZOOKEEPER=1 to enable ZooKeeper.

  1. Start a Pulsar SQL worker:
  1. ./bin/pulsar sql-worker run
  1. After initializing Pulsar standalone cluster and the SQL worker, run SQL CLI:
  1. ./bin/pulsar sql
  1. Test with SQL commands:
  1. trino> show catalogs;
  2.  Catalog
  3. ---------
  4.  pulsar
  5.  system
  6. (2 rows)
  8. Query 20180829_211752_00004_7qpwh, FINISHED, 1 node
  9. Splits: 19 total, 19 done (100.00%)
  10. 0:00 [0 rows, 0B] [0 rows/s, 0B/s]
  13. trino> show schemas in pulsar;
  14.         Schema
  15. -----------------------
  16.  information_schema
  17.  public/default
  18.  public/functions
  19. (3 rows)
  21. Query 20180829_211818_00005_7qpwh, FINISHED, 1 node
  22. Splits: 19 total, 19 done (100.00%)
  23. 0:00 [4 rows, 89B] [21 rows/s, 471B/s]
  26. trino> show tables in pulsar."public/default";
  27.  Table
  28. -------
  29. (0 rows)
  31. Query 20180829_211839_00006_7qpwh, FINISHED, 1 node
  32. Splits: 19 total, 19 done (100.00%)
  33. 0:00 [0 rows, 0B] [0 rows/s, 0B/s]

Since there is no data in Pulsar, no records are returned.

  1. Start the built-in connector DataGeneratorSource and ingest some mock data:
  1. ./bin/pulsar-admin sources create --name generator --destinationTopicName generator_test --source-type data-generator

And then you can query a topic in the namespace “public/default”:

  1. trino> show tables in pulsar."public/default";
  2.      Table
  3. ----------------
  4.  generator_test
  5. (1 row)
  7. Query 20180829_213202_00000_csyeu, FINISHED, 1 node
  8. Splits: 19 total, 19 done (100.00%)
  9. 0:02 [1 rows, 38B] [0 rows/s, 17B/s]

You can now query the data within the topic “generator_test”:

  1. trino> select * from pulsar."public/default".generator_test;
  3.   firstname  | middlename  |  lastname   |              email               |   username   | password | telephonenumber | age |                 companyemail                  | nationalidentitycardnumber |
  4. -------------+-------------+-------------+----------------------------------+--------------+----------+-----------------+-----+-----------------------------------------------+----------------------------+
  5.  Genesis     | Katherine   | Wiley       | genesis.wiley@gmail.com          | genesisw     | y9D2dtU3 | 959-197-1860    |  71 | genesis.wiley@interdemconsulting.eu           | 880-58-9247                |
  6.  Brayden     |             | Stanton     | brayden.stanton@yahoo.com        | braydens     | ZnjmhXik | 220-027-867     |  81 | brayden.stanton@supermemo.eu                  | 604-60-7069                |
  7.  Benjamin    | Julian      | Velasquez   | benjamin.velasquez@yahoo.com     | benjaminv    | 8Bc7m3eb | 298-377-0062    |  21 | benjamin.velasquez@hostesltd.biz              | 213-32-5882                |
  8.  Michael     | Thomas      | Donovan     | donovan@mail.com                 | michaeld     | OqBm9MLs | 078-134-4685    |  55 | michael.donovan@memortech.eu                  | 443-30-3442                |
  9.  Brooklyn    | Avery       | Roach       | brooklynroach@yahoo.com          | broach       | IxtBLafO | 387-786-2998    |  68 | brooklyn.roach@warst.biz                      | 085-88-3973                |
  10.  Skylar      |             | Bradshaw    | skylarbradshaw@yahoo.com         | skylarb      | p6eC6cKy | 210-872-608     |  96 | skylar.bradshaw@flyhigh.eu                    | 453-46-0334                |
  11. ...

You can query the mock data.