load data into vertica without create table schema

Have large files with multiple hundreds of columns. Is there anyway to load into vertica table without creating table definition first?

Comments

  • Flex Tables allows you to do this. You can just "CREATE FLEX TABLE MyTable();" then COPY into it using the FDelimitedParser() or FJSONParser().

    A one-page HOWTO can be downloaded at: http://my.vertica.com/docs/7.0.x/PDF/HP_Vertica_7.0.x_FlextablesQuickstart.pdf
  • Got error when create flex table, Error: syntax error at or near "flex"
  • Couldn't find the link on myvertica to download Flex Zone.  Anyone know where I can get it?
  • FlexZone, and Flex Tables, are shipped as part of Vertica 7.  What version of Vertica are you running?
  • Unfortunately, Flex requires Vertica 7...

    If you have an older version, you could write a little script that generates an appropriate giant CREATE TABLE statement.  If you don't know the type of a column, make it a VARCHAR column; you can add a new column later of the appropriate data type and use casts to convert it.
  • Also, if you're running your own Vertica CE instance, Vertica 7 is available for download at http://my.vertica.com/ .
  • Adam:
    It's not a problem to write a giant create table statement nor define varchar datatype and make changes later.  It's time consuming, and unnecssary to do so when it's a one time data load, or the files have dynamic columns.  Just want to see if I can do that with Vertica, as I'm evaluating the product.  I do not find CE version on my.vertica.com/downloads.  

  • Hm...  A Vertica 7 CE version is definitely available at that URL; we have lots of people using it.  You do have to create a (free) account first at that site; the URL only works if you're logged in.

    You can also find it at the new Vertica Marketplace:  http://vertica.com/marketplace  (also various other sites online.)

  • @Adam:

    Can i use flex tables to load csv files? I am finding it pretty difficult if i have to define 40 odd data column types manually for 100 tables.
  • Hi Ammol,

    It looks like you've asked this question in three different places on the forums.  Please ask your questions in just one place.  What happens if you don't, is, you will get three slightly-different partial answers from three different people, rather than having all of the answers on one thread so the responders can clarify each others' responses and answer your question completely.

    It looks like you have gotten a more-detailed answer elsewhere.  To briefly answer your question here as well:  Yes, Flex Tables were designed to help with exactly this scenario.  See the Flex documentation for details.

    Adam
  • HI Adam,

    My apologies for the oversight.

    Can you please help me out on how we can copy data from a flex table to a normal table? and if the process can be scheduled or something?

    Regards

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file