Record definition for a sprayed file

[kaliyugantagonist]

Hi,

I have attached a file that has the record in my csv(as csv file upload is not allowed in the forum)I have sprayed onto a HPCC cluster using ECLWatch.

Logical Name: .::adcampaign_twitter_data

I'm using HPCC JDBC Driver to access the sprayed data. The query is :

Code: Select all

String stmtsql = "select tbl.* from .::adcampaign_twitter_data tbl where User_ID='294766'";

The error that I'm getting is as follows:

Code: Select all

2013/01/29 07:05:28.664 - HPCCStatementAttempting to process sql query: select tbl.* from .::adcampaign_twitter_data tbl where User_ID='294766'
2013/01/29 07:05:28.664 - INCOMING SQL: select tbl.* from .::adcampaign_twitter_data tbl where User_ID='294766'
2013/01/29 07:05:28.679 - Fetching file information: http://10.101.2.170:8010/WsDfu/DFUQuery?LogicalName=.::ADCAMPAIGN_TWITTER_DATA&rawxml_&filetype=Logical Files and Superfiles
2013/01/29 07:05:28.726 - .::adcampaign_twitter_data does not appear to contain a valid record definition.
java.sql.SQLException: HPCCStatement:  Cannot execute SQL command
   Invalid or forbidden table found: .::ADCAMPAIGN_TWITTER_DATA
   at org.hpccsystems.jdbcdriver.HPCCStatement.executeHPCCQuery(HPCCStatement.java:110)
   at org.hpccsystems.jdbcdriver.HPCCStatement.execute(HPCCStatement.java:255)
   at org.hpccsystems.jdbcdriver.HPCCStatement.execute(HPCCStatement.java:245)
   at org.hpccsystems.jdbcdriver.tests.HPCCDriverTest.main(HPCCDriverTest.java:1355)
2013/01/29 07:05:28.726 - Invalid or forbidden table found: .::ADCAMPAIGN_TWITTER_DATA

As per the HPCC JDBC documentation :

Note: The HPCC JDBC driver only supports files which contain the record definition in the logical file’s
metadata. Sprayed files do not contain this metadata. This metadata exists on any file or index which is
written to the HPCC Distributed File System. Sprayed data files typically undergo some processing and an
OUTPUT of the transformed data to disk before use, so this should not interfere with the driver’s usefulness.

In my case, how must I upload the file onto HPCC - I used the word upload because sprayed files don't seem to be supported by HPCC-JDBC ! Shall I use ECL Plus, DFU etc. for upload?Or something else?

Thanks and regards !

[rtaylor]

Metadata about a dataset is maintained by the DFU and gets written when the OUTPUT action writes the file to disk. To make the sprayed file available to the JDBC driver, just define the sprayed file then write it to disk (with a new filename) with OUTPUT -- this will give the JDBC driver the metadata it needs.

HTH,

Richard

[[email protected]]

Kaliyugantagonist, as Richard pointed out, adding a record definition to a file involves defining the data structure, loading the data into an HPCC dataset, and OUTPUTing the dataset out to an HPCC file...

something like this:

twitter_data_structure :=RECORD
type fieldname1;
type fieldname2;
...
type fieldnameN;
END;

twitter_data = DATASET('.::adcampaign_twitter_data', twitter_data_structure, CSV);
OUTPUT(twitter_data, , '.::hpcc::adcampaign_twitter_data');

now you should be able to query '.::hpcc::adcampaign_twitter_data' using SQL through the jdbc driver. Let us know if there are any questions/concerns/comments. Thanks.

[kaliyugantagonist]

Hello everyone,

Thanks for the inputs - they helped a lot !
Please treat this question as closed.

Thanks and regards !