Support Questions

Find answers, ask questions, and share your expertise
Announcements
Celebrating as our community reaches 100,000 members! Thank you!

Creating Iceberg table

avatar

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

1 ACCEPTED SOLUTION

avatar
Master Collaborator

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;

View solution in original post

8 REPLIES 8

avatar
Contributor

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?


avatar

I created the table using the Impala editor

avatar
Contributor

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.

avatar
Master Collaborator

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;

avatar

Hi,

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

avatar

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

avatar
Community Manager

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



Regards,

Vidya Sargur,
Community Manager


Was your question answered? Make sure to mark the answer as the accepted solution.
If you find a reply useful, say thanks by clicking on the thumbs up button.
Learn more about the Cloudera Community:

avatar
Contributor

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