Load JSON file into Vertica

Mike_Gong_1

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

[Deleted User]

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_Leybovic

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_Shama

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?