question Re: Creating Iceberg table in Support Questions

Creating Iceberg table

EFasdfSDfaSDFG — Tue, 04 Jun 2024 07:18:07 GMT

Hello,

Complete noob here.

I have a file, and have created an iceberg table over it (via Hue). However, I can't select any data. How do I set the schema up correctly?

CREATE TABLE iceberg_test_1 (month STRING, day STRING, time STRING, host STRING, source STRING, body STRING) PARTITIONED BY(event_day INT, event_month INT, event_year INT) STORED BY ICEBERG LOCATION 'ofs://ozone/user/logs/test';

The file is:
Jun 4 08:26:57 host smartd[2657]: Device: /dev/bus/0 [megaraid_disk_05], SMART Failure: DATA CHANNEL IMPENDING FAILURE GENERAL HARD DRIVE FAILURE

Re: Creating Iceberg table

Mounika_Reddy — Tue, 04 Jun 2024 10:27:08 GMT

Hi ,
Can you confirm with the version details also let us know you are using Hue--Hive Editor or Hue--Impala editor to create the iceberg table?

Re: Creating Iceberg table

EFasdfSDfaSDFG — Tue, 04 Jun 2024 12:22:50 GMT

I created the table using the Impala editor

Re: Creating Iceberg table

Mounika_Reddy — Tue, 04 Jun 2024 15:23:41 GMT

Hi ,
Below is the syntax for creating iceberg tables using impala
1)create table test(id int, name string) stored by iceberg;
insert into test values(1,"M");

2)create table test_iceberg (id int, name string) stored by iceberg;
LOAD DATA INPATH '<path_of_file>' INTO TABLE test_iceberg;

3)CREATE EXTERNAL TABLE test_iceberg_1
STORED AS ICEBERG
LOCATION '/path/to/table'
TBLPROPERTIES('iceberg.catalog'='hadoop.tables');

If the data exits in ozone make sure you have the enough privileges to access the same

For more info ,we suggest you to refer the https://impala.apache.org/docs/build/html/topics/impala_iceberg.html

Hope the info helps.

Re: Creating Iceberg table

RangaReddy — Wed, 05 Jun 2024 09:03:19 GMT

Hi @EFasdfSDfaSDFG

Use the following syntax to create a Iceberg table using Impala Editor in Hue:

Impala: CREATE TABLE IF NOT EXISTS ice_t (i int, s string, ts timestamp, d date) STORED BY ICEBERG; INSERT INTO ice_t VALUES(1, 'Ranga', '2015-05-15 12:00:00', '2015-05-15'); select * from ice_t; CREATE TABLE IF NOT EXISTS ice_ext (i int, s string, ts timestamp, d date) PARTITIONED BY (state string) STORED BY ICEBERG; INSERT INTO ice_ext SELECT 1, 'Ranga', '2015-05-15 12:00:00', '2015-05-15', 'Andhra'; select * from ice_ext; CREATE TABLE ice_t2 ( i int, s string, ts timestamp, d date ) STORED AS ICEBERG LOCATION '/warehouse/tablespace/external/hive/ice_t2'; INSERT INTO ice_t2 VALUES(1, 'Ranga', '2015-05-15 12:00:00', '2015-05-15'); select * from ice_t2;

Re: Creating Iceberg table

VidyaSargur — Tue, 11 Jun 2024 05:26:36 GMT

@EFasdfSDfaSDFG, Did the response assist in resolving your query? If it did, kindly mark the relevant reply as the solution, as it will aid others in locating the answer more easily in the future.

Re: Creating Iceberg table

EFasdfSDfaSDFG — Tue, 11 Jun 2024 06:15:56 GMT

Hi,

From those examples, can I create an external Iceberg table over a file/directory the same way that Hive is able to?

Re: Creating Iceberg table

EFasdfSDfaSDFG — Tue, 11 Jun 2024 06:52:12 GMT

So I figured something out:

use my_db;
CREATE EXTERNAL TABLE IF NOT EXISTS a_iceberg_1 (the_month STRING, the_day INT, the_time INT, host STRING, message STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY ' ' STORED AS TEXTFILE LOCATION 'ofs://path/to/file' TBLPROPERTIES('iceberg.catalog'='hadoop.tables')

Only now I have to figure out how to delimit the fields properly:
Jun 4 03:10:01 host rsyslogd: [origin software="rsyslogd" swVersion="8.24.0-57.el7_9.3" x-pid="3829" x-info="http://www.rsyslog.com"] rsyslogd was HUPed

Re: Creating Iceberg table

Mounika_Reddy — Mon, 17 Jun 2024 14:30:19 GMT

Hi @EFasdfSDfaSDFG
From Hive the following formats supported : Parquet (default), Avro, ORC
Create table examples:
CREATE EXTERNAL TABLE test_ice_1 ( i INT, t TIMESTAMP, j BIGINT) STORED BY ICEBERG;
CREATE EXTERNAL TABLE test_ice_2 (i INT, t TIMESTAMP) PARTITIONED BY (j BIGINT) STORED BY ICEBERG;
CREATE EXTERNAL TABLE test_ice_3 (i int) STORED AS ORC STORED BY ICEBERG LOCATION '';
CREATE EXTERNAL TABLE test_ice_4 (i int) STORED BY ICEBERG TBLPROPERTIES ('key'='value', 'key'='value')
CREATE EXTERNAL TABLE test_ice_1 (i int) STORED AS ORC STORED BY ICEBERG TBLPROPERTIES ('format-version' = '2');