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 10 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 8 months ago

    Please post videos in English language

  6. alhanouf 4 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 4 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 months 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.