Re: [luciddb-users] What to do to physically import the data to LucidDB?

[Nicholas G. <ngo...@ba...>]

On Feb 24, 2012, at 2:38 PM, bibudh wrote:

> LucidDB. Initially I created a JDBC FDW. Now, as per the following post,
> Lucid does not physically import the data, but imports only the metadata. 
Yes... Foreign Data Wrappers are a metadata and live connection (data resides in foreign system) approach.

> FlatFileFennelRel at the lowest level, and not FennelValuesRel. Does this
> mean the data is physically only in the files and not in Lucid? 
Yes.

> and use them successfully? Also, for MedJdbcQueryRel queries, are the
> indexes already in the PostgreSQL DB used? 
When you access a foreign data source, and you've used a predicate that the JDBC connector knows how to push down (ie, select c1 from foreign.table1 where c1 = 2) then the remote database will receive a database with that filter/pushdown.  If there are indexes in PG that know how to improve the performance of that pushed down query (ie, where c1 = 2) then yes, those indices will help.

> rel#1445:TableModificationRel.NONE(child=HepRelVertex#1446,table=[LOCALDB,
> EXTRACTION_SCHEMA,
> people],operation=INSERT,updateColumnList=[],flattened=true); see trace for
> partially optimized plan. Details: reason is [Node's traits (NONE) do not
> match required traits (ITERATOR)]; while preparing statement [explain plan
> for insert into EXTRACTION_SCHEMA."people" select * from
> sandbox."public"."people"]. (state=,code=0)

Can you post the DDL for the table?  How did you create EXTRACTION_SCHEMA."people" table? Also, have you verfied that you're getting data if you read the file?  On one of the smaller remote tables do a select * to make sure you are reading columns/data values.

In short the steps to load data into LucidDB are:

1) Create foreign wrapper (sandbox)
2) Import foreign tables metadata operation (import statement, resulting in the "people" table being present)
3) Test foreign table (select * from sandbox."people" where name = 'blah')
4) Create a table in LucidDB to receive the data (create table extraction_schema.people ( c1 int, c2 varchar(100))
5) Load table (insert into extraction_schema.people (c1, c2) select c1, c2 from sandbox."people")
6) Create any indices/etc on the LucidDB table (extraction_schema.people)

Nick