Load JSON file into Vertica

Mike_Gong_1Mike_Gong_1 Registered User
I heard data in JSON file can be directly loaded into Vertica. Can you please shed a light on how to do? Thanks


  • Vertica has not publicly released a JSON parser at this time. However, we often have private betas going on. To that end, you should e-mail these folks: http://www.vertica.com/2013/06/17/a-sneak-peek-of-hp-vertica-pulse-harnessing-the-volume-and-velocity-of-social-media-data/ In general, if you want to parse some format that Vertica doesn't offer any support for, you can add support yourself using the UDParser C++ API in our SDK. There are a number of example parsers installed to /opt/vertica/sdk/examples/ on your cluster; also on http://github.com/vertica/ . There's also always this approach: http://www.vertica.com/2012/11/14/how-to-parse-anything-into-vertica-using-externalfilter/
  • Daniel_LeybovicDaniel_Leybovic Registered User
    I did MongoDB <-> Vertica connector, that can extract/import tables to file or directly to MongoDB (from MongoDB to Vertica - it's very hard, because MongoDB is schema-less).
    When community will regulate agreement of marketplace I will upload it to market.
  • Bharathan_ShamaBharathan_Shama Registered User
    I am currently working with Vertica 6.1.2. Can I import a JSON file using an R-UDF with its main function looking somewhat like this -
    rjson <- function(x)  {  library(RJSONIO)  library(plyr)  jsonfile<-fromJSON(x,nullValue = NULL)  json_table<-do.call("rbind.fill", lapply(jsonfile, as.data.frame))  json_table  }  rjsonFactory<-function()  {  list(name=rjson, udxtype=c("transform"),intype=c("any"),outtype=c("any"),outtypecallback= rjsonReturnType)  }  rjsonReturnType <- function(x)  {  ret = data.frame(datatype = rep(NA,1), length = rep(NA,1),scale = rep(NA,1), name = rep(NA,1))  ret  }
    With the R-UDF written this way, I went on to create a library and a transform function. But when I invoke the UDF, I get this error message - 
    ERROR 3399:  Failure in UDx RPC call InvokeGetReturnType(): Error calling getReturnType() in User Defined Object [rjson] at [/scratch_a/release/vbuild/vertica/UDxFence/vertica-udx-R.cpp:289], error code: 0, message: Error happened in getReturnType : Datatype must be specified for return and parameter types
    I am a novice. Could you point me in the right direction?

Leave a Comment

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