Quantcast

What to do to physically import the data to LucidDB?

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

What to do to physically import the data to LucidDB?

bibudh

   Hi,
    I am trying to import 5 tables from my transactional PostgreSQL DB to 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.

    http://www.luciddb.org/forums/discussion/353/-pertaining-to-indexes-on-foreign-tables./p1

    This was evident when I did EXPLAIN PLAN for one of the queries. I could see MedJdbcQueryRel at the lowest level, with foreignSql statements, rather than FennelValuesRel.

    However, after this, I exported my data to .csv files and loaded using a file wrapper. After that, when I do EXPLAIN PLAN for a query, I see FlatFileFennelRel at the lowest level, and not FennelValuesRel. Does this mean the data is physically only in the files and not in Lucid?

    My question is, what do I need to do to make sure the data gets PHYSICALLY loaded to the LucidDB tables, so that I can create bitmap indexes and use them successfully? Also, for MedJdbcQueryRel queries, are the indexes already in the PostgreSQL DB used?

    I have already tried the following for import

    insert into EXTRACTION_SCHEMA."people" select * from sandbox."public"."people";

    but it gives the following error:

    Error: Optimizer failed to find a valid physical implementation for relational expression 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)

    Also, I guess import is probably not the preferred method for tables with millions of rows?

    Thanks
             Bibudh
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: What to do to physically import the data to LucidDB?

Mark Cahill
It's just a link. You'd have to create a table with the fields you want in
another database in LucidDB, and do a INSERT INTO [LucidDB table] SELECT *
FROM [postgres table]

Mark Cahill
Data Analyst
YouVersion





On 2/24/12 5:38 PM, "bibudh" <[hidden email]> wrote:

>
>   Hi,
>    I am trying to import 5 tables from my transactional PostgreSQL DB to
>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.
>
>  
>http://www.luciddb.org/forums/discussion/353/-pertaining-to-indexes-on-for
>eign-tables./p1
>
>    This was evident when I did EXPLAIN PLAN for one of the queries. I
>could
>see MedJdbcQueryRel at the lowest level, with foreignSql statements,
>rather
>than FennelValuesRel.
>
>    However, after this, I exported my data to .csv files and loaded
>using a
>file wrapper. After that, when I do EXPLAIN PLAN for a query, I see
>FlatFileFennelRel at the lowest level, and not FennelValuesRel. Does this
>mean the data is physically only in the files and not in Lucid?
>
>    My question is, what do I need to do to make sure the data gets
>PHYSICALLY loaded to the LucidDB tables, so that I can create bitmap
>indexes
>and use them successfully? Also, for MedJdbcQueryRel queries, are the
>indexes already in the PostgreSQL DB used?
>
>    I have already tried the following for import
>
>    insert into EXTRACTION_SCHEMA."people" select * from
>sandbox."public"."people";
>
>    but it gives the following error:
>
>    Error: Optimizer failed to find a valid physical implementation for
>relational expression
>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)
>
>    Also, I guess import is probably not the preferred method for tables
>with millions of rows?
>
>    Thanks
>             Bibudh
>
>--
>View this message in context:
>http://luciddb-users.1374590.n2.nabble.com/What-to-do-to-physically-import
>-the-data-to-LucidDB-tp7316306p7316306.html
>Sent from the luciddb-users mailing list archive at Nabble.com.
>
>--------------------------------------------------------------------------
>----
>Virtualization & Cloud Management Using Capacity Planning
>Cloud computing makes use of virtualization - but cloud computing
>also focuses on allowing computing to be delivered as a service.
>http://www.accelacomm.com/jaw/sfnl/114/51521223/
>_______________________________________________
>luciddb-users mailing list
>[hidden email]
>https://lists.sourceforge.net/lists/listinfo/luciddb-users


------------------------------------------------------------------------------
Virtualization & Cloud Management Using Capacity Planning
Cloud computing makes use of virtualization - but cloud computing
also focuses on allowing computing to be delivered as a service.
http://www.accelacomm.com/jaw/sfnl/114/51521223/
_______________________________________________
luciddb-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/luciddb-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: What to do to physically import the data to LucidDB?

Nicholas Goodman
In reply to this post by bibudh
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
------------------------------------------------------------------------------
Virtualization & Cloud Management Using Capacity Planning
Cloud computing makes use of virtualization - but cloud computing
also focuses on allowing computing to be delivered as a service.
http://www.accelacomm.com/jaw/sfnl/114/51521223/
_______________________________________________
luciddb-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/luciddb-users
Loading...