Query and write data in Apache Iceberg via Tabular.io using StarRocks

[alberttwong]

Prerequisites

For this tutorial you need to:

• Have a MySQL client.
• A StarRocks or CelerData database cluster.
• An account on Tabular.io
• Creating a table in Tabular.io
• Create a StarRocks External Catalog and query the data.
• Query the sandbox data on Tabular.io
• Write data on Tabular.io

Have a MySQL client

This is out of scope for the tutorial.

A StarRocks or CelerData database cluster

This is out of scope for the tutorial.

An account on Tabular.io

This is out of scope for the tutorial.

Creating a table in Tabular.io

Use the following URL to create a table in Tabular.io https://tabular.io/blog/how-to-create-a-table-in-tabular/. This will store the movies data in an Apache Iceberg table in S3.

Since StarRocks doesn't support AVRO files yet, you'll have to modify the create table to create the table using parquet.

CREATE TABLE default.movies STORED AS PARQUET AS SELECT * from movies ;

Create a StarRocks External Catalog and query the data

Now that you have data, let's connect to that Iceberg environment.
Tip: When you need to use the "default" database in Tabular, use single quotes around the default and system database. An example is "use tabular.`default`".

drop catalog tabular;
create external catalog tabular properties (
  "type"="iceberg",
  "iceberg.catalog.type"="rest",
  "iceberg.catalog.uri"="https://api.tabular.io/ws",
  "iceberg.catalog.credential"="XXXXXXXX",
  "iceberg.catalog.warehouse"="personal"
);
set catalog tabular;
show databases from tabular;
use tabular.`default`;
select count(*) from movies;
select * from `default`.movies limit 10;

Results show this:

StarRocks > show databases from tabular;
+----------+
| Database |
+----------+
| default  |
| examples |
| system   |
+----------+
3 rows in set (0.59 sec)
StarRocks > use tabular.`default`;
Database changed
StarRocks > show tables;
select count(*) from movies;
+-------------------+
| Tables_in_default |
+-------------------+
| movies            |
+-------------------+
1 rows in set (2.16 sec)

StarRocks > select count(*) from movies;
+----------+
| count(*) |
+----------+
|    62423 |
+----------+
1 row in set (1.59 sec)

StarRocks > select count(*) from `default`.movies;
+----------+
| count(*) |
+----------+
|    62423 |
+----------+
1 row in set (2.10 sec)

StarRocks > select * from `default`.movies limit 10;
+---------+------------------------------------+---------------------------------------------+
| movieId | title                              | genres                                      |
+---------+------------------------------------+---------------------------------------------+
|       1 | Toy Story (1995)                   | Adventure|Animation|Children|Comedy|Fantasy |
|       2 | Jumanji (1995)                     | Adventure|Children|Fantasy                  |
|       3 | Grumpier Old Men (1995)            | Comedy|Romance                              |
|       4 | Waiting to Exhale (1995)           | Comedy|Drama|Romance                        |
|       5 | Father of the Bride Part II (1995) | Comedy                                      |
|       6 | Heat (1995)                        | Action|Crime|Thriller                       |
|       7 | Sabrina (1995)                     | Comedy|Romance                              |
|       8 | Tom and Huck (1995)                | Adventure|Children                          |
|       9 | Sudden Death (1995)                | Action                                      |
|      10 | GoldenEye (1995)                   | Action|Adventure|Thriller                   |
+---------+------------------------------------+---------------------------------------------+
10 rows in set (2.00 sec)

Query the sandbox data on Tabular.io

StarRocks > use tabular.examples;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
StarRocks > select count(*) from nyc_taxi_locations;
+----------+
| count(*) |
+----------+
|      265 |
+----------+
1 row in set (7.60 sec)

Write data on Tabular.io

StarRocks > create table bar(id int, name string) ;
Query OK, 0 rows affected (1.08 sec)

StarRocks > insert into bar values (100, 'aaa');
Query OK, 1 row affected (2.92 sec)
{'label':'FAKE_ICEBERG_SINK_LABEL', 'status':'VISIBLE', 'txnId':'-1'}

StarRocks > select * from bar;
+------+------+
| id   | name |
+------+------+
|  100 | aaa  |
+------+------+
1 row in set (1.36 sec)

and another example

StarRocks > set catalog tabular;
Query OK, 0 rows affected (0.00 sec)

StarRocks > show databases from tabular;
+----------+
| Database |
+----------+
| default  |
| examples |
| system   |
+----------+
3 rows in set (3.00 sec)

StarRocks > use tabular.`default`;
Database changed
StarRocks > show tables;
+-------------------+
| Tables_in_default |
+-------------------+
| movies            |
+-------------------+
1 row in set (2.40 sec)

StarRocks > select * from movies limit 10;
+---------+------------------------------------+---------------------------------------------+
| movieId | title                              | genres                                      |
+---------+------------------------------------+---------------------------------------------+
|       1 | Toy Story (1995)                   | Adventure|Animation|Children|Comedy|Fantasy |
|       2 | Jumanji (1995)                     | Adventure|Children|Fantasy                  |
|       3 | Grumpier Old Men (1995)            | Comedy|Romance                              |
|       4 | Waiting to Exhale (1995)           | Comedy|Drama|Romance                        |
|       5 | Father of the Bride Part II (1995) | Comedy                                      |
|       6 | Heat (1995)                        | Action|Crime|Thriller                       |
|       7 | Sabrina (1995)                     | Comedy|Romance                              |
|       8 | Tom and Huck (1995)                | Adventure|Children                          |
|       9 | Sudden Death (1995)                | Action                                      |
|      10 | GoldenEye (1995)                   | Action|Adventure|Thriller                   |
+---------+------------------------------------+---------------------------------------------+
10 rows in set (2.98 sec)

StarRocks > insert into movies values (99999, 'albert', 'wong');
Query OK, 1 row affected (6.80 sec)
{'label':'FAKE_ICEBERG_SINK_LABEL', 'status':'VISIBLE', 'txnId':'-1'}

StarRocks > select * from movies where movieid=99999;
+---------+-------------------------------+----------------+
| movieId | title                         | genres         |
+---------+-------------------------------+----------------+
|   99999 | albert                        | wong           |
|   99999 | Texas Across the River (1966) | Comedy|Western |
+---------+-------------------------------+----------------+
2 rows in set (1.90 sec)

[alanpaulkwan]

It might just be a two sentence modification, but could you show how to do this with MinIO?

[alberttwong]

I'd look at #23427

[alberttwong]

Here is something more complex and would be more representative of an analytics query.

drop catalog tabularalbert;
create external catalog tabularalbert properties (
  "type"="iceberg",
  "iceberg.catalog.type"="rest",
  "iceberg.catalog.uri"="https://api.tabular.io/ws",
  "iceberg.catalog.credential"="XXXYYYYY",
  "iceberg.catalog.warehouse"="personal"
);
set catalog tabularalbert;
show databases from tabularalbert;
use tabularalbert.`default`;
use tabularalbert.`examples`;

select * from `examples`.nyc_taxi_yellow limit 10;
create table if not exists tabularalbert.`default`.taxi_tip_rate_per_passenger_celerdata2 as
select
passenger_count,
 count(*) as trip_count,
 avg(tip_amount / fare_amount) as tip_rate
from tabularalbert.`examples`.nyc_taxi_yellow
where
pickup_time between '2020-03-01' and '2020-03-31'
  and passenger_count > 0
  and fare_amount > 0
group by passenger_count;
DROP TABLE  tabularalbert.`default`.taxi_tip_rate_per_passenger_celerdata2

[atifiu]

Which version of iceberg you are using and will starrocks supports iceberg version 2 (row level delete) table in future and if yes by when(any roadmap)

[alberttwong]

You can get those details by contacting CelerData, the main contributor to the StarRocks code base.

[alberttwong]

#24659

[atifiu]

Link says starrocks support read on iceberg v2 tables but in documentation it says only v1

[alberttwong]

Link says starrocks support read on iceberg v2 tables but in documentation it says only v1

The docs always lag. It's best to try to see if it works. Also this is a community project so we would appreciate pr to the docs.