Skip to main content
EFasdfSDfaSDFG
EFasdfSDfaSDFG
Silver ⭐️⭐️⭐️
June 4, 2024
Solved

Creating Iceberg table

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

    RangaReddy
    RangaReddyAnswer
    Gold ⭐️
    June 5, 2024

    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 in thread

    8 replies

    Mounika_Reddy
    Gold ⭐️
    Mounika_Reddy
    Gold ⭐️
    June 4, 2024

    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?


    Translate
      EFasdfSDfaSDFG
      EFasdfSDfaSDFGAuthor
      Silver ⭐️⭐️⭐️
      June 4, 2024

      I created the table using the Impala editor

      Translate
      Mounika_Reddy
      Gold ⭐️
      Mounika_Reddy
      Gold ⭐️
      June 4, 2024

      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.

      Translate
      RangaReddy
      Gold ⭐️
      RangaReddyAnswer
      Gold ⭐️
      June 5, 2024

      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;
      Translate
        EFasdfSDfaSDFG
        EFasdfSDfaSDFGAuthor
        Silver ⭐️⭐️⭐️
        June 11, 2024

        Hi,

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

        Translate
        EFasdfSDfaSDFG
        EFasdfSDfaSDFGAuthor
        Silver ⭐️⭐️⭐️
        June 11, 2024

        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

        Translate
        VidyaSargur
        Community Manager
        VidyaSargur
        Community Manager
        June 11, 2024

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

        Translate
        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.
        Mounika_Reddy
        Gold ⭐️
        Mounika_Reddy
        Gold ⭐️
        June 17, 2024

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

        Translate
        Terms & ConditionsAccessibility statement