Home Forums Hive / HCatalog Hive CLI is not able to create tables from another table or view

This topic contains 4 replies, has 2 voices, and was last updated by  Yi Zhang 10 months ago.

  • Creator
    Topic
  • #41866

    Paul
    Member

    I have been trying to create a table containing a column from another table, but Hive CLI consistently fails to do so.

    The following is the query:

    CREATE TABLE tweets_id_sample AS
    SELECT
    id
    FROM tweets_sample;

    The same query above works in Hive Beeswax. I have been consistently successful in creating these types of queries in Hive Beeswax.

    The following are instances that work with my Hive CLI:

    – The query above also works if a view is created instead of a table.
    – Empty tables can be created
    – Tables from HDFS files can be created (e.g. the tweets_sample table found from the first code block was created from HDFS files

    Currently, I’m stuck on how to fix this issue. I would greatly appreciate it if you can help me fix my Hive CLI and/or lead me closer to doing so.

    Thanks in advance!

    Other notes:

    The environment that I’m working on is as follows:

    – Hortonworks Sandbox v1.3 on Oracle VM VirtualBox
    – I was working on Hortonworks Tutorial #13
    – Hive Beeswax queries are executed via Hue UI from user ‘hue’
    – Hive CLI queries are executed from user ‘root’ (and also tested from user ‘hue’)

    I’m not sure if reposting from another forum is okay. Please let me know if it is not. Here is a reference to my stackoverflow question in which I was able to include more details: http://stackoverflow.com/q/19608576/872420

Viewing 4 replies - 1 through 4 (of 4 total)

You must be logged in to reply to this topic.

  • Author
    Replies
  • #42871

    Yi Zhang
    Moderator

    Hi Paul,

    Thanks for sharing the solution! Now think about it, it is okay to create the view, probably because the view is not actually executed until it is used.

    Thanks,
    Yi

    Collapse
    #42752

    Paul
    Member

    Hi Yi.

    I was able to identify the cause of the errors. Here is the complete answer that I supplied to the stackoverflow question: http://stackoverflow.com/a/19763704/872420

    In summary, the error that I experienced was caused by the select-from clause being applied to a source table stored in JSON SerDe row format. This can be solved by configuring Hive to add a jar via Hive CLI to its class path as follows:

    hive> ADD JAR [path to JSON SerDe jar file];

    For example:

    hive> ADD JAR /usr/lib/hive/lib/json-serde-1.1.4-jar-with-dependencies.jar;

    Hive will confirm the addition by returning the following statement:

    Added /usr/lib/hive/lib/json-serde-1.1.4-jar-with-dependencies.jar to class path
    Added resource: /usr/lib/hive/lib/json-serde-1.1.4-jar-with-dependencies.jar

    The above should be done at every Hive session.

    Collapse
    #42620

    Yi Zhang
    Moderator

    Hi Paul,

    Sorry that was my mistake stating that create table has to state columns.

    What is the error when you try to create table as select from hive cli?

    Thanks,
    Yi

    Collapse
    #41970

    Paul
    Member

    Hi Yi.

    Thanks for your reply. I thought that CREATE will also derive the schema from the underlying table similar to a VIEW, does it not? Here’s a reference to the Apache Hive documentation: https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL#LanguageManualDDL-CreateTableAsSelect%28CTAS%29

    Still, I tried your suggestion, but Hive CLI is returning another error as follows:


    FAILED: SemanticException [Error 10065]: CREATE TABLE AS SELECT command cannot specify the list of columns for the target table

    I still haven’t figured out why this is happening.

    Collapse
Viewing 4 replies - 1 through 4 (of 4 total)