[MonetDB-users] Change schema for already existing tables - users-list - monetdb.org

newer
[MonetDB-users] Crash during COPY...

[MonetDB-users] Change schema for already existing tables

older
[MonetDB-users] Problems with COPY...

Travis Brady

21 Jan 2008 21 Jan '08

10:06 p.m.

Hello, Is it possible to change the schema for tables that have already been created and populated? I'd like to avoid having to fully recreate everything as the load process will likely take a few days at least. thank you, Travis Brady

Attachments:

attachment.html (text/html — 377 bytes)

Reply

Sign in to reply online Use email software

Show replies by date

Martin Kersten

21 Jan 21 Jan

10:50 p.m.

Travis Brady wrote:

Hello, Hello Travis you can at any time add and drop columns without any reorganization cost. regards, Martin

Is it possible to change the schema for tables that have already been created and populated? I'd like to avoid having to fully recreate everything as the load process will likely take a few days at least.

thank you, Travis Brady ------------------------------------------------------------------------

------------------------------------------------------------------------- This SF.net email is sponsored by: Microsoft Defy all challenges. Microsoft(R) Visual Studio 2008. http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/ ------------------------------------------------------------------------

_______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

Reply

Sign in to reply online Use email software

Bob Ippolito

11:09 p.m.

Martin, What Travis meant is that we have a bunch of tables in the default sys schema and we would like to move them to a different schema, hopefully without losing a day or two worth of ETL. On that note what is the most efficient way to do ETL? We're using COPY INTO right now and it works but we're only getting between 200-2000 rows/sec and mserver5 has crashed on us twice now for no apparent reason. -bob On Jan 21, 2008 2:50 PM, Martin Kersten wrote:

Travis Brady wrote:

...
Hello, Hello Travis you can at any time add and drop columns without any reorganization cost. regards, Martin

...
Is it possible to change the schema for tables that have already been created and populated? I'd like to avoid having to fully recreate everything as the load process will likely take a few days at least.

thank you, Travis Brady ------------------------------------------------------------------------

------------------------------------------------------------------------- This SF.net email is sponsored by: Microsoft Defy all challenges. Microsoft(R) Visual Studio 2008. http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/ ------------------------------------------------------------------------

_______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

------------------------------------------------------------------------- This SF.net email is sponsored by: Microsoft Defy all challenges. Microsoft(R) Visual Studio 2008. http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/ _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

Reply

Sign in to reply online Use email software

Niels Nes

22 Jan 22 Jan

8:28 a.m.

On Mon, Jan 21, 2008 at 03:09:09PM -0800, Bob Ippolito wrote:

Martin,

Bob

What Travis meant is that we have a bunch of tables in the default sys schema and we would like to move them to a different schema, hopefully without losing a day or two worth of ETL. Using a create table + select into is the only solution. Tables from other schemas are accessable via full qualified names, ie schema.table.column.

On that note what is the most efficient way to do ETL? We're using COPY INTO right now and it works but we're only getting between 200-2000 rows/sec and mserver5 has crashed on us twice now for no apparent reason.

A couple of bugs related to ETL processing were fixed, only in the cvs head. The main reason of this (I know) incorrect place was that we expect to do a release of this branch soon. Your coalesce and timestamp problem aren't fixed however. About The slow loading problem, we would need to know your db schema ie a snippet of your sql create table statement would be needed. Also ETL involves more then just loading, ie COPY into, so could it be the transformation part or isn't that part of MonetDB's task? Niels

-bob

On Jan 21, 2008 2:50 PM, Martin Kersten wrote:

...
Travis Brady wrote:

...
Hello, Hello Travis you can at any time add and drop columns without any reorganization cost. regards, Martin

...
Is it possible to change the schema for tables that have already been created and populated? I'd like to avoid having to fully recreate everything as the load process will likely take a few days at least.

thank you, Travis Brady ------------------------------------------------------------------------

------------------------------------------------------------------------- This SF.net email is sponsored by: Microsoft Defy all challenges. Microsoft(R) Visual Studio 2008. http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/ ------------------------------------------------------------------------

_______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

------------------------------------------------------------------------- This SF.net email is sponsored by: Microsoft Defy all challenges. Microsoft(R) Visual Studio 2008. http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/ _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

------------------------------------------------------------------------- This SF.net email is sponsored by: Microsoft Defy all challenges. Microsoft(R) Visual Studio 2008. http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/ _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

-- Niels Nes, Centre for Mathematics and Computer Science (CWI) Kruislaan 413, 1098 SJ Amsterdam, The Netherlands room C0.02, phone ++31 20 592-4098, fax ++31 20 592-4312 url: http://www.cwi.nl/~niels e-mail: Niels.Nes@cwi.nl

Reply

Sign in to reply online Use email software

Bob Ippolito

10:21 a.m.

On Jan 22, 2008 12:28 AM, Niels Nes wrote:

On Mon, Jan 21, 2008 at 03:09:09PM -0800, Bob Ippolito wrote:

...
Martin,

Bob

...
What Travis meant is that we have a bunch of tables in the default sys schema and we would like to move them to a different schema, hopefully without losing a day or two worth of ETL. Using a create table + select into is the only solution. Tables from other schemas are accessable via full qualified names, ie schema.table.column.

It's too late now, since SR5 crashed on us with an unrecoverable database we had to start over anyway. This time we did it with a proper schema :)

...
On that note what is the most efficient way to do ETL? We're using COPY INTO right now and it works but we're only getting between 200-2000 rows/sec and mserver5 has crashed on us twice now for no apparent reason.

A couple of bugs related to ETL processing were fixed, only in the cvs head. The main reason of this (I know) incorrect place was that we expect to do a release of this branch soon. Your coalesce and timestamp problem aren't fixed however.

About The slow loading problem, we would need to know your db schema ie a snippet of your sql create table statement would be needed. Also ETL involves more then just loading, ie COPY into, so could it be the transformation part or isn't that part of MonetDB's task?

monetdb is *only* doing COPY INTO, all of the other ETL work is done by PostgreSQL and Python. Mostly it's Python asking PostgreSQL to COPY its data into a file in a format that MonetDB can read. I have sent you a copy of our schema off-list.

Niels

...
-bob

On Jan 21, 2008 2:50 PM, Martin Kersten wrote:

...
Travis Brady wrote:

...
Hello, Hello Travis you can at any time add and drop columns without any reorganization cost. regards, Martin

...
Is it possible to change the schema for tables that have already been created and populated? I'd like to avoid having to fully recreate everything as the load process will likely take a few days at least.

thank you, Travis Brady ------------------------------------------------------------------------

------------------------------------------------------------------------- This SF.net email is sponsored by: Microsoft Defy all challenges. Microsoft(R) Visual Studio 2008. http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/ ------------------------------------------------------------------------

_______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

------------------------------------------------------------------------- This SF.net email is sponsored by: Microsoft Defy all challenges. Microsoft(R) Visual Studio 2008. http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/ _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

------------------------------------------------------------------------- This SF.net email is sponsored by: Microsoft Defy all challenges. Microsoft(R) Visual Studio 2008. http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/ _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

--

Niels Nes, Centre for Mathematics and Computer Science (CWI) Kruislaan 413, 1098 SJ Amsterdam, The Netherlands room C0.02, phone ++31 20 592-4098, fax ++31 20 592-4312 url: http://www.cwi.nl/~niels e-mail: Niels.Nes@cwi.nl

------------------------------------------------------------------------- This SF.net email is sponsored by: Microsoft Defy all challenges. Microsoft(R) Visual Studio 2008. http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/ _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

Reply

Sign in to reply online Use email software

m h

4:31 p.m.

On Jan 22, 2008 3:21 AM, Bob Ippolito wrote:

On Jan 22, 2008 12:28 AM, Niels Nes wrote: ...

...
About The slow loading problem, we would need to know your db schema ie a snippet of your sql create table statement would be needed. Also ETL involves more then just loading, ie COPY into, so could it be the transformation part or isn't that part of MonetDB's task?

monetdb is *only* doing COPY INTO, all of the other ETL work is done by PostgreSQL and Python. Mostly it's Python asking PostgreSQL to COPY its data into a file in a format that MonetDB can read.

Bob- I've a python script (SQLAlchemy) that dumps Oracle data into a delimited file. I've used that file to load into monetdb, postgres and mysql (using the native bulk loader for the respective dbs, ie COPY INTO not sqlalchemy). Let me know if you are interested in comparing dump formats. -matt

I have sent you a copy of our schema off-list.

Reply

Sign in to reply online Use email software

6185

Age (days ago)

6186

Last active (days ago)

Download

5 comments

5 participants

tags

participants (5)

Bob Ippolito
m h
Martin Kersten
Niels Nes
Travis Brady