Sun Aug 14, 2022 10:41 pm
Login Register Lost Password? Contact Us

Please Note: The HPCC Systems forums are moving to Stack Overflow. We invite you to post your questions on Stack Overflow utilizing the tag hpcc-ecl (https://stackoverflow.com/search?tab=newest&q=hpcc-ecl). This legacy forum will be active and monitored during our transition to Stack Overflow but will become read only beginning September 1, 2022.



HSQL - Tables - how to load from .csv and logical files?

Questions around writing code and queries

Tue Feb 08, 2022 5:12 pm Change Time Zone

Hi,

Could anybody already working with this tool be so kind to provide (or point me to) a couple of small code examples on how to load flat csv or json files to a table, and the same with logical files inside HPCCSystems?
I couldn't find any complete example of these actions in the current documentation.

It will be much appreciated.
Thank you !!!
Warm regards
Ricardo
rfernandez2007
 
Posts: 25
Joined: Mon Nov 29, 2021 2:40 pm

Tue Feb 08, 2022 7:27 pm Change Time Zone

Hi Ricardo,

The following should work, assuming you have a CSV file with the given name:

Code: Select all
export simpleLayout = CREATE TABLE (
  personID  INTEGER,
  name string,
  age integer,
  wage real,
  hasHouse integer
);

export simpleTable = select * from '~hsql::testfiles::simpledata.csv'  type csv layout simpleLayout offset 1;


This was taken from one of the test files, but it was also modified (the file used LAYOUT as a record definition keyword rather than CREATE TABLE and the syntax was modified recently).

Hope this helps!

Dan
DSC
Community Advisory Board Member
Community Advisory Board Member
 
Posts: 571
Joined: Tue Oct 18, 2011 4:45 pm

Wed Feb 09, 2022 12:26 am Change Time Zone

Hi Dan,
Indeed it helps!
My apologies for not having found these examples in the project tree before. Although as you said the syntax is different in more than one aspect.

I tried the code and it complained about the file not having a table alias on this line:
Code: Select all
export simpleTable = select * from '~simpledata.csv' as aliasname  type csv layout simpleLayout offset 1;



Code: Select all
[{
   "resource": "/f:/DataLake/HPCCSystems/HSQL-Projects/LoadFile.hsql",
   "owner": "_generated_diagnostic_collection_name_#1",
   "severity": 2,
   "message": "No TABLE alias provided, using __r_action_0",
   "startLineNumber": 9,
   "startColumn": 36,
   "endLineNumber": 9,
   "endColumn": 70
}]


And as expected this is the ecl code generated:

Code: Select all
export LoadFile := MODULE
EXPORT simpleLayout := {INTEGER personid,STRING name,INTEGER age,REAL wage,INTEGER hashouse};
__r_action_1 := FUNCTION
__r_action_0 := DATASET('~simpledata.csv',simpleLayout,CSV);
__r_action_2 := TABLE(__r_action_0,{ __r_action_0 });
__r_action_3 := __r_action_2[2..];
RETURN __r_action_3;
END;
EXPORT simpleTable := __r_action_1;
END;


I couldn't find a way to add that alias. Using as aliasname triggers a lot of other errors: (way above my understanding)

Code: Select all
[{
   "resource": "/f:/DataLake/HPCCSystems/HSQL-Projects/LoadFile.hsql",
   "owner": "_generated_diagnostic_collection_name_#1",
   "severity": 8,
   "message": "No actions can be used if shared or export is used",
   "startLineNumber": 1,
   "startColumn": 1,
   "endLineNumber": 10,
   "endColumn": 1
},{
   "resource": "/f:/DataLake/HPCCSystems/HSQL-Projects/LoadFile.hsql",
   "owner": "_generated_diagnostic_collection_name_#1",
   "severity": 8,
   "message": "no viable alternative at input ''simpledata.csv'as'",
   "startLineNumber": 9,
   "startColumn": 53,
   "endLineNumber": 9,
   "endColumn": 53
},{
   "resource": "/f:/DataLake/HPCCSystems/HSQL-Projects/LoadFile.hsql",
   "owner": "_generated_diagnostic_collection_name_#1",
   "severity": 8,
   "message": "mismatched input 'as' expecting SEMICOLON",
   "startLineNumber": 9,
   "startColumn": 53,
   "endLineNumber": 9,
   "endColumn": 53
},{
   "resource": "/f:/DataLake/HPCCSystems/HSQL-Projects/LoadFile.hsql",
   "owner": "_generated_diagnostic_collection_name_#1",
   "severity": 8,
   "message": "mismatched input 'type' expecting '='",
   "startLineNumber": 9,
   "startColumn": 67,
   "endLineNumber": 9,
   "endColumn": 67
},{
   "resource": "/f:/DataLake/HPCCSystems/HSQL-Projects/LoadFile.hsql",
   "owner": "_generated_diagnostic_collection_name_#1",
   "severity": 8,
   "message": "mismatched input 'offset' expecting '='",
   "startLineNumber": 9,
   "startColumn": 96,
   "endLineNumber": 9,
   "endColumn": 96
}]


I give you all this information just in case it helps improving something.

In summary:

1. I compiled it anyway although it has the alias error.
2. It generated the ugly .ecl file
3. I defined a kind of BWR to execute the definitions
4. I run the files a few times with no results, until I realized the file was expected to be in the cluster and not locally (I felt dumb for a few seconds ;-) )
5. I uploaded the .csv to the landing zone and sprayed it
6. Then it worked

My conclusion is that for using this you still have to have your share of ECL and HPCCSystems knowledge. (mine being still too weak)

Now I will step forward into generating visuals and doing some ML. So, you can bet you'll here from me again :D .
At least I hope it helps someone else running into the same problem.

Thank you very much!!!
All the best !!
rfernandez2007
 
Posts: 25
Joined: Mon Nov 29, 2021 2:40 pm

Wed Feb 09, 2022 1:01 pm Change Time Zone

Thank you very much for the feedback, Ricardo! HSQL is a work-in-progress so some rough edges are to be expected, but some of the items you found (e.g. aliasing) are really a little rougher than they should be. I will get this feedback incorporated into our issue tracker.

Thanks again!

Dan
DSC
Community Advisory Board Member
Community Advisory Board Member
 
Posts: 571
Joined: Tue Oct 18, 2011 4:45 pm

Wed Feb 09, 2022 9:25 pm Change Time Zone

Hi Ricardo,
Please try following:

export simpleLayout = CREATE TABLE (
personID INTEGER,
name string,
age integer,
wage real,
hasHouse integer
);

export simpleTable = select * from '~simpledata.csv' type csv layout simpleLayout as TestMe offset 1;
Bahar
 
Posts: 1
Joined: Wed Feb 09, 2022 8:53 pm


Return to Programming

Who is online

Users browsing this forum: No registered users and 1 guest

cron