The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.
Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

Pig connector - query for parameters

Hi All,
I'm trying to use the Pig connector as per docs but doesn't seem to be working if you specify a query to get the parameters. It works fine with a static list of parameters but not with a query.

- This works:
t = LOAD 'sql://{SELECT c1, c2, c3, c4 FROM tbl WHERE key = ? };{1,2,3,4,5}' USING com.vertica.pig.VerticaLoader(...);

- This doesn't work (doesn't return anything because parameters are wrong).
t = LOAD 'sql://{SELECT c1, c2, c3, c4 FROM tbl WHERE key = ? };sql://{ SELECT DISTINCT key FROM tbl }' USING com.vertica.pig.VerticaLoader(...);
* the query sent to Vertica (as per query_profiles) looks like:
SELECT c1, c2, c3 FROM tbl WHERE key = 'SELECT DISTINCT key FROM tbl'

huh ?!

Looking in git (I guess that's the code used to build latest pig-vertica.jar) I see this method:
public void setLocation(String location, Job job) throws IOException {:
if (params != null && !params.isEmpty()) {
         if (params.startsWith("sql://")) {
                          params = params.substring("sql://{".length()).replace('}', ' ');
                          setParameters(params); }
        else {
                   params = params.replaceAll("^\\s*\\{","");
                   params = params.replaceAll("\\}\\s*$","");

that apparently in case the params is a query doesn't do anything else than extracting the query string throwing away the sql:// and {} and setting it as the parameter ! Shouldn't it execute the query, get the result and build a csv list of the values (to match the else branch ?!)

Am I missing something ? Anybody using it ? Is this implemented (as stated in the docs) or not ?


  • Or set the proper VerticaInputFormat and let the that extract proper parameter values based on query...

    @Overridepublic InputFormat getInputFormat() throws IOException { return new VerticaInputFormat(getQuery(), getParameters()); }

    Will take a better look at the code but if anybody uses this pls let me know if I'm missing something.
  • Ok... after digging a bit in the code I found the issue, it is supported but you're not supposed to use SELECT with capitals ... that's not supported :))

    public List<InputSplit> getSplits(JobContext context) throws IOException {
    if (params != null && params.startsWith("select")) {

Leave a Comment

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