The Hortonworks Community Connection is now live. A completely rebuilt Q&A forum, Knowledge Base, Code Hub and more, backed by the experts in the industry.

You will be redirected here in 10 seconds. If your are not redirected, click here to visit the new site.

The legacy Hortonworks Forum is now closed. You can view a read-only version of the former site by clicking here. The site will be taken offline on January 31,2016

Hive / HCatalog Forum

Hive CLI is not able to create tables from another table or view

  • #41866

    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
    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:

  • Author
  • #41970

    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:

    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.

    Yi Zhang

    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?



    Hi Yi.

    I was able to identify the cause of the errors. Here is the complete answer that I supplied to the stackoverflow question:

    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.

    Yi Zhang

    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.


The forum ‘Hive / HCatalog’ is closed to new topics and replies.

Support from the Experts

A HDP Support Subscription connects you experts with deep experience running Apache Hadoop in production, at-scale on the most demanding workloads.

Enterprise Support »

Become HDP Certified

Real world training designed by the core architects of Hadoop. Scenario-based training courses are available in-classroom or online from anywhere in the world

Training »

Hortonworks Data Platform
The Hortonworks Data Platform is a 100% open source distribution of Apache Hadoop that is truly enterprise grade having been built, tested and hardened with enterprise rigor.
Get started with Sandbox
Hortonworks Sandbox is a self-contained virtual machine with Apache Hadoop pre-configured alongside a set of hands-on, step-by-step Hadoop tutorials.
Modern Data Architecture
Tackle the challenges of big data. Hadoop integrates with existing EDW, RDBMS and MPP systems to deliver lower cost, higher capacity infrastructure.