Create Hive tables with headers and load quoted CSV data

Create Hive tables with headers and load quoted CSV data

Hue makes it easy to create Hive tables.

With HUE-1746, Hue guesses the columns names and types (int, string, float…) directly by looking at your data. If your data starts with a header, this one will automatically be used and skipped while creating the table.

Quoted CSV fields are also compatible thanks to HUE-1747.

Here is the data file used:

http://www.fdic.gov/bank/individual/failed/banklist.html

 

This is the SerDe for reading quoted CSV:

https://github.com/ogrodnek/csv-serde

 

And the command to switch the SerDe used by the table:

ALTER TABLE banks SET SERDE 'com.bizo.hive.serde.csv.CSVSerde'

Now go analyze the data with the Hive, Impala or Pig editors!

13 Comments

  1. clancey 4 years ago

    accent was a little difficult to understand but incredibly helpful! thank you for uploading

  2. Max Dumas 4 years ago

    Allo Romain
    I tried with Impala after running fine in Hive and get this error:
    “AnalysisException: Failed to load metadata for table: default.banks CAUSED BY: TableLoadingException: Failed to load metadata for table: banks CAUSED BY: InvalidStorageDescriptorException: Impala does not support tables of this type. REASON: SerDe library ‘com.bizo.hive.serde.csv.CSVSerde’ is not supported.”

  3. SwapnilG 2 years ago

    Hi Romain,

    I am getting following error in the last step, while altering the table:
    Error while processing statement: invalid url: maprfs:////user/mapr/csv-serde-1.1.2-0.11.0-all.jar, expecting ( file | hdfs | ivy) as url scheme.

    As per your directions, the command I ran to alter my table is:
    ALTER TABLE crash_data SET SERDE ‘com.bizo.hive.serde.csv.CSVSerde’;

    I am using mapr cluster and I have already uploaded csv serde jar using HUE file browser. Is it necessary to move csv serde jar file on maprfs in order to make this query work?

    • Hue Team 2 years ago

      This is an error coming from Hive not Hue right?

  4. Ryan 8 months ago

    The download link on the github page for the CSV Support tool is broken. The file doesn’t exist anymore.

  5. aakanksha jain 6 months ago

    Please post videos in English language

  6. alhanouf 2 months ago

    when i upload my data from csv file and browse it the sample state that “The table does not contain any data.”

    • Author
      Hue Team 2 months ago

      Typically the LOAD DATA did not work. Do you see an error about this in the logs? Is the parent folder of the file 777 permissioned?

      • Crispin Velez 2 weeks ago

        Hi! I am having the same error. The MetaData or Schema for the Table loads. In fact the table is created but the Data/information is not loaded. I can even see it in the preview and the last step sample view. When I submit the job everything is created but the uploading of the information.

        I am using the hueadmin user, I can create a db in the hive datawarehouse, I believe I should be able to create a table.

        Any ideas?

Leave a reply

Your email address will not be published. Required fields are marked *

*

This site uses Akismet to reduce spam. Learn how your comment data is processed.