Creating Iceberg table

[#1]

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

[#2]

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?

[#3]

I created the table using the Impala editor

[#4]

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.

[#5 · Accepted solution]

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;

[#6]

Hi,

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

[#7]

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

[#8]

@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. 

[#9]

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');