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.
  2. ./bin/pulsar standalone
  1. Start a Pulsar SQL worker.
  2. ./bin/pulsar sql-worker run
  1. After initializing Pulsar standalone cluster and the SQL worker, run SQL CLI.
  2. ./bin/pulsar sql
  1. Test with SQL commands.
  2. presto> show catalogs;
  3.  Catalog 
  4. ---------
  5.  pulsar  
  6.  system  
  7. (2 rows)
  9. Query 20180829_211752_00004_7qpwh, FINISHED, 1 node
  10. Splits: 19 total, 19 done (100.00%)
  11. 0:00 [0 rows, 0B] [0 rows/s, 0B/s]
  14. presto> show schemas in pulsar;
  15.         Schema         
  16. -----------------------
  17.  information_schema    
  18.  public/default        
  19.  public/functions      
  20.  sample/standalone/ns1 
  21. (4 rows)
  23. Query 20180829_211818_00005_7qpwh, FINISHED, 1 node
  24. Splits: 19 total, 19 done (100.00%)
  25. 0:00 [4 rows, 89B] [21 rows/s, 471B/s]
  28. presto> show tables in pulsar."public/default";
  29.  Table 
  30. -------
  31. (0 rows)
  33. Query 20180829_211839_00006_7qpwh, FINISHED, 1 node
  34. Splits: 19 total, 19 done (100.00%)
  35. 0:00 [0 rows, 0B] [0 rows/s, 0B/s]

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

  1. Start the built-in connector DataGeneratorSource and ingest some mock data.
  2. ./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”.

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

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

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

You can query the mock data.

Query your own data

If you want to query your own data, you need to ingest your own data first. You can write a simple producer and write custom defined data to Pulsar. The following is an example.

  2. public class TestProducer {
  4.     public static class Foo {
  5.         private int field1 = 1;
  6.         private String field2;
  7.         private long field3;
  9.         public Foo() {
  10.         }
  12.         public int getField1() {
  13.             return field1;
  14.         }
  16.         public void setField1(int field1) {
  17.             this.field1 = field1;
  18.         }
  20.         public String getField2() {
  21.             return field2;
  22.         }
  24.         public void setField2(String field2) {
  25.             this.field2 = field2;
  26.         }
  28.         public long getField3() {
  29.             return field3;
  30.         }
  32.         public void setField3(long field3) {
  33.             this.field3 = field3;
  34.         }
  35.     }
  37.     public static void main(String[] args) throws Exception {
  38.         PulsarClient pulsarClient = PulsarClient.builder().serviceUrl("pulsar://localhost:6650").build();
  39.         Producer<Foo> producer = pulsarClient.newProducer(AvroSchema.of(Foo.class)).topic("test_topic").create();
  41.         for (int i = 0; i < 1000; i++) {
  42.             Foo foo = new Foo();
  43.             foo.setField1(i);
  44.             foo.setField2("foo" + i);
  45.             foo.setField3(System.currentTimeMillis());
  46.             producer.newMessage().value(foo).send();
  47.         }
  48.         producer.close();
  49.         pulsarClient.close();
  50.     }
  51. }