You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@trafodion.apache.org by Pierre Smits <pi...@gmail.com> on 2017/03/07 13:47:56 UTC
Dumping a schema
Hi all,
Is there in Trafodion an equivalent for dumping a schema, that consist of:
- dropping the table(s) on exist,
- creating the table(s) and index(es),
- setting the permissions,
- loading the data
Best regards,
Pierre Smits
ORRTIZ.COM <http://www.orrtiz.com>
OFBiz based solutions & services
OFBiz Extensions Marketplace
http://oem.ofbizci.net/oci-2/
Re: Dumping a schema
Posted by Pierre Smits <pi...@gmail.com>.
Hi Suresh,
In my book any improvement that leads to increased adoption (and thus to
more contributors aka community growth) is worthy of an JIRA issue. ;-)
The reason I questioned about a dump mechanism a la pq_dump is that I, in
effecting the OFBiz-Trafodion Integration PoC, experienced a multi-hour
load session when creating the db structure through OFBiz. This makes is
very time consuming when considering doing OFBiz multi-tenancy in
combination with Trafodion, as each tenant gets it own set of schemas (3,
with each set encompassing about a 1000 tables, and even more keys (primary
and foreign) and indexes).
I don't know, but being able to an initial dump that can be reused for each
tenant should potentially lead to less time required to set one up. Hours
more required in combination with Trafodion (keep in mind in a virtualbox
sandbox) is way off from less than 30 mins on a OFBiz-MySQL or
OFBiz-PostgreSQL).
Best regards,
Pierre Smits
ORRTIZ.COM <http://www.orrtiz.com>
OFBiz based solutions & services
OFBiz Extensions Marketplace
http://oem.ofbizci.net/oci-2/
On Mon, Mar 13, 2017 at 5:18 PM, Suresh Subbiah <su...@gmail.com>
wrote:
> Hi Pierre,
>
> I have been looking postGreSQL's dump utility. Trafodion has some of that
> functionality today but not all. We could implement missing data dump
> features using HBase's exportsnapshot functionality. Do you think a JIRA is
> in order? If you could provide information about what you had in mind, I
> can create the JIRA.
>
> Thanks
> Suresh
>
>
> On Wed, Mar 8, 2017 at 8:26 AM, Eric Owhadi <er...@esgyn.com> wrote:
>
> > Hello Pierre,
> > Did you look at the ddl.txt file? In Suresh example, this is where you
> > should see the "script file".
> > To replay it, using sqlci, just "OBEY ddl.txt"
> > Does that make sense?
> > Eric
> >
> > -----Original Message-----
> > From: Pierre Smits [mailto:pierre.smits@gmail.com]
> > Sent: Wednesday, March 8, 2017 4:22 AM
> > To: dev@trafodion.incubator.apache.org
> > Subject: Re: Dumping a schema
> >
> > Hi Suresh, all,
> >
> > What I am looking for, and which I think will help adoption of our
> > product, is an equivalent to the PostgreSQL pq_dump (see [1]) or the
> MySQL
> > mysqldump funcitons, and the subsequent import functions.
> >
> > While testing at PURGEDATA function within the sqlci in my
> OFBiz-Trafodion
> > PoC environment I didn't see any output. The SHOWDLL function delivered
> me
> > a set of create statements for a table (including those for associated
> > indexes)
> >
> > But I did not see how to export this to a script file.
> >
> > [1] https://www.postgresql.org/docs/9.1/static/app-pgdump.html
> >
> > Best regards,
> >
> > Pierre Smits
> >
> > ORRTIZ.COM <http://www.orrtiz.com>
> > OFBiz based solutions & services
> >
> > OFBiz Extensions Marketplace
> > http://oem.ofbizci.net/oci-2/
> >
> > On Tue, Mar 7, 2017 at 6:06 PM, Suresh Subbiah <
> suresh.subbiah60@gmail.com
> > >
> > wrote:
> >
> > > Hi Pierre,
> > >
> > > I think PURGEDATA will do the first three steps, while loading new
> > > data will have to be done as usual. Now PURGEDATA is for a single
> > > table at a time, so it have to be repeated for every table.
> > >
> > > For example
> > >
> > > PURGEDATA <table-name> ;
> > > LOAD INTO <table-name> SELECT * FROM <source-table-name> ; //
> > > <source-table-name> can refer to HBase or Hive table // other load
> > > statements can be used if source data in text files.
> > >
> > > Trafodion also has a DROP SCHEMA <schema-name> CASCADE statemet, that
> > > will drop all tables and indexes in a schema. But steps two to four
> > > would have to be done separately.
> > >
> > > SHOWDDL <table-name> ;
> > > on each table in the schema can be done before the DROP SCHEMA
> > > cascade. The resulting log file can then be obeyed to recreate
> > > tables/indexes and set permissions.
> > >
> > > For example
> > >
> > > log ddl.txt ;
> > > SHOWDDL <table1> ;
> > > SHOWDDL <table2> ;
> > > ....
> > > log ;
> > > DROP SCHEMA <schema-name> CASCADE ;
> > >
> > > CREATE SCHEMA <schema-name> ;
> > > OBEY ddl.txt ;
> > > LOAD INTO <table1> .... ;
> > > LOAD INTO <table2> .... ;
> > >
> > > Thank you
> > > Suresh
> > >
> > > On Tue, Mar 7, 2017 at 7:47 AM, Pierre Smits <pi...@gmail.com>
> > > wrote:
> > >
> > > > Hi all,
> > > >
> > > > Is there in Trafodion an equivalent for dumping a schema, that
> > > > consist
> > > of:
> > > >
> > > > - dropping the table(s) on exist,
> > > > - creating the table(s) and index(es),
> > > > - setting the permissions,
> > > > - loading the data
> > > >
> > > > Best regards,
> > > >
> > > > Pierre Smits
> > > >
> > > > ORRTIZ.COM <http://www.orrtiz.com>
> > > > OFBiz based solutions & services
> > > >
> > > > OFBiz Extensions Marketplace
> > > > http://oem.ofbizci.net/oci-2/
> > > >
> > >
> >
>
Re: Dumping a schema
Posted by Suresh Subbiah <su...@gmail.com>.
Hi Pierre,
I have been looking postGreSQL's dump utility. Trafodion has some of that
functionality today but not all. We could implement missing data dump
features using HBase's exportsnapshot functionality. Do you think a JIRA is
in order? If you could provide information about what you had in mind, I
can create the JIRA.
Thanks
Suresh
On Wed, Mar 8, 2017 at 8:26 AM, Eric Owhadi <er...@esgyn.com> wrote:
> Hello Pierre,
> Did you look at the ddl.txt file? In Suresh example, this is where you
> should see the "script file".
> To replay it, using sqlci, just "OBEY ddl.txt"
> Does that make sense?
> Eric
>
> -----Original Message-----
> From: Pierre Smits [mailto:pierre.smits@gmail.com]
> Sent: Wednesday, March 8, 2017 4:22 AM
> To: dev@trafodion.incubator.apache.org
> Subject: Re: Dumping a schema
>
> Hi Suresh, all,
>
> What I am looking for, and which I think will help adoption of our
> product, is an equivalent to the PostgreSQL pq_dump (see [1]) or the MySQL
> mysqldump funcitons, and the subsequent import functions.
>
> While testing at PURGEDATA function within the sqlci in my OFBiz-Trafodion
> PoC environment I didn't see any output. The SHOWDLL function delivered me
> a set of create statements for a table (including those for associated
> indexes)
>
> But I did not see how to export this to a script file.
>
> [1] https://www.postgresql.org/docs/9.1/static/app-pgdump.html
>
> Best regards,
>
> Pierre Smits
>
> ORRTIZ.COM <http://www.orrtiz.com>
> OFBiz based solutions & services
>
> OFBiz Extensions Marketplace
> http://oem.ofbizci.net/oci-2/
>
> On Tue, Mar 7, 2017 at 6:06 PM, Suresh Subbiah <suresh.subbiah60@gmail.com
> >
> wrote:
>
> > Hi Pierre,
> >
> > I think PURGEDATA will do the first three steps, while loading new
> > data will have to be done as usual. Now PURGEDATA is for a single
> > table at a time, so it have to be repeated for every table.
> >
> > For example
> >
> > PURGEDATA <table-name> ;
> > LOAD INTO <table-name> SELECT * FROM <source-table-name> ; //
> > <source-table-name> can refer to HBase or Hive table // other load
> > statements can be used if source data in text files.
> >
> > Trafodion also has a DROP SCHEMA <schema-name> CASCADE statemet, that
> > will drop all tables and indexes in a schema. But steps two to four
> > would have to be done separately.
> >
> > SHOWDDL <table-name> ;
> > on each table in the schema can be done before the DROP SCHEMA
> > cascade. The resulting log file can then be obeyed to recreate
> > tables/indexes and set permissions.
> >
> > For example
> >
> > log ddl.txt ;
> > SHOWDDL <table1> ;
> > SHOWDDL <table2> ;
> > ....
> > log ;
> > DROP SCHEMA <schema-name> CASCADE ;
> >
> > CREATE SCHEMA <schema-name> ;
> > OBEY ddl.txt ;
> > LOAD INTO <table1> .... ;
> > LOAD INTO <table2> .... ;
> >
> > Thank you
> > Suresh
> >
> > On Tue, Mar 7, 2017 at 7:47 AM, Pierre Smits <pi...@gmail.com>
> > wrote:
> >
> > > Hi all,
> > >
> > > Is there in Trafodion an equivalent for dumping a schema, that
> > > consist
> > of:
> > >
> > > - dropping the table(s) on exist,
> > > - creating the table(s) and index(es),
> > > - setting the permissions,
> > > - loading the data
> > >
> > > Best regards,
> > >
> > > Pierre Smits
> > >
> > > ORRTIZ.COM <http://www.orrtiz.com>
> > > OFBiz based solutions & services
> > >
> > > OFBiz Extensions Marketplace
> > > http://oem.ofbizci.net/oci-2/
> > >
> >
>
RE: Dumping a schema
Posted by Eric Owhadi <er...@esgyn.com>.
Hello Pierre,
Did you look at the ddl.txt file? In Suresh example, this is where you should see the "script file".
To replay it, using sqlci, just "OBEY ddl.txt"
Does that make sense?
Eric
-----Original Message-----
From: Pierre Smits [mailto:pierre.smits@gmail.com]
Sent: Wednesday, March 8, 2017 4:22 AM
To: dev@trafodion.incubator.apache.org
Subject: Re: Dumping a schema
Hi Suresh, all,
What I am looking for, and which I think will help adoption of our product, is an equivalent to the PostgreSQL pq_dump (see [1]) or the MySQL mysqldump funcitons, and the subsequent import functions.
While testing at PURGEDATA function within the sqlci in my OFBiz-Trafodion PoC environment I didn't see any output. The SHOWDLL function delivered me a set of create statements for a table (including those for associated
indexes)
But I did not see how to export this to a script file.
[1] https://www.postgresql.org/docs/9.1/static/app-pgdump.html
Best regards,
Pierre Smits
ORRTIZ.COM <http://www.orrtiz.com>
OFBiz based solutions & services
OFBiz Extensions Marketplace
http://oem.ofbizci.net/oci-2/
On Tue, Mar 7, 2017 at 6:06 PM, Suresh Subbiah <su...@gmail.com>
wrote:
> Hi Pierre,
>
> I think PURGEDATA will do the first three steps, while loading new
> data will have to be done as usual. Now PURGEDATA is for a single
> table at a time, so it have to be repeated for every table.
>
> For example
>
> PURGEDATA <table-name> ;
> LOAD INTO <table-name> SELECT * FROM <source-table-name> ; //
> <source-table-name> can refer to HBase or Hive table // other load
> statements can be used if source data in text files.
>
> Trafodion also has a DROP SCHEMA <schema-name> CASCADE statemet, that
> will drop all tables and indexes in a schema. But steps two to four
> would have to be done separately.
>
> SHOWDDL <table-name> ;
> on each table in the schema can be done before the DROP SCHEMA
> cascade. The resulting log file can then be obeyed to recreate
> tables/indexes and set permissions.
>
> For example
>
> log ddl.txt ;
> SHOWDDL <table1> ;
> SHOWDDL <table2> ;
> ....
> log ;
> DROP SCHEMA <schema-name> CASCADE ;
>
> CREATE SCHEMA <schema-name> ;
> OBEY ddl.txt ;
> LOAD INTO <table1> .... ;
> LOAD INTO <table2> .... ;
>
> Thank you
> Suresh
>
> On Tue, Mar 7, 2017 at 7:47 AM, Pierre Smits <pi...@gmail.com>
> wrote:
>
> > Hi all,
> >
> > Is there in Trafodion an equivalent for dumping a schema, that
> > consist
> of:
> >
> > - dropping the table(s) on exist,
> > - creating the table(s) and index(es),
> > - setting the permissions,
> > - loading the data
> >
> > Best regards,
> >
> > Pierre Smits
> >
> > ORRTIZ.COM <http://www.orrtiz.com>
> > OFBiz based solutions & services
> >
> > OFBiz Extensions Marketplace
> > http://oem.ofbizci.net/oci-2/
> >
>
Re: Dumping a schema
Posted by Pierre Smits <pi...@gmail.com>.
Hi Suresh, all,
What I am looking for, and which I think will help adoption of our product,
is an equivalent to the PostgreSQL pq_dump (see [1]) or the MySQL mysqldump
funcitons, and the subsequent import functions.
While testing at PURGEDATA function within the sqlci in my OFBiz-Trafodion
PoC environment I didn't see any output. The SHOWDLL function delivered me
a set of create statements for a table (including those for associated
indexes)
But I did not see how to export this to a script file.
[1] https://www.postgresql.org/docs/9.1/static/app-pgdump.html
Best regards,
Pierre Smits
ORRTIZ.COM <http://www.orrtiz.com>
OFBiz based solutions & services
OFBiz Extensions Marketplace
http://oem.ofbizci.net/oci-2/
On Tue, Mar 7, 2017 at 6:06 PM, Suresh Subbiah <su...@gmail.com>
wrote:
> Hi Pierre,
>
> I think PURGEDATA will do the first three steps, while loading new data
> will have to be done as usual. Now PURGEDATA is for a single table at a
> time, so it have to be repeated for every table.
>
> For example
>
> PURGEDATA <table-name> ;
> LOAD INTO <table-name> SELECT * FROM <source-table-name> ; //
> <source-table-name> can refer to HBase or Hive table
> // other load statements can be used if source data in text files.
>
> Trafodion also has a DROP SCHEMA <schema-name> CASCADE statemet, that will
> drop all tables and indexes in a schema. But steps two to four would have
> to be done separately.
>
> SHOWDDL <table-name> ;
> on each table in the schema can be done before the DROP SCHEMA cascade. The
> resulting log file can then be obeyed to recreate tables/indexes and set
> permissions.
>
> For example
>
> log ddl.txt ;
> SHOWDDL <table1> ;
> SHOWDDL <table2> ;
> ....
> log ;
> DROP SCHEMA <schema-name> CASCADE ;
>
> CREATE SCHEMA <schema-name> ;
> OBEY ddl.txt ;
> LOAD INTO <table1> .... ;
> LOAD INTO <table2> .... ;
>
> Thank you
> Suresh
>
> On Tue, Mar 7, 2017 at 7:47 AM, Pierre Smits <pi...@gmail.com>
> wrote:
>
> > Hi all,
> >
> > Is there in Trafodion an equivalent for dumping a schema, that consist
> of:
> >
> > - dropping the table(s) on exist,
> > - creating the table(s) and index(es),
> > - setting the permissions,
> > - loading the data
> >
> > Best regards,
> >
> > Pierre Smits
> >
> > ORRTIZ.COM <http://www.orrtiz.com>
> > OFBiz based solutions & services
> >
> > OFBiz Extensions Marketplace
> > http://oem.ofbizci.net/oci-2/
> >
>
Re: Dumping a schema
Posted by Suresh Subbiah <su...@gmail.com>.
Hi Pierre,
I think PURGEDATA will do the first three steps, while loading new data
will have to be done as usual. Now PURGEDATA is for a single table at a
time, so it have to be repeated for every table.
For example
PURGEDATA <table-name> ;
LOAD INTO <table-name> SELECT * FROM <source-table-name> ; //
<source-table-name> can refer to HBase or Hive table
// other load statements can be used if source data in text files.
Trafodion also has a DROP SCHEMA <schema-name> CASCADE statemet, that will
drop all tables and indexes in a schema. But steps two to four would have
to be done separately.
SHOWDDL <table-name> ;
on each table in the schema can be done before the DROP SCHEMA cascade. The
resulting log file can then be obeyed to recreate tables/indexes and set
permissions.
For example
log ddl.txt ;
SHOWDDL <table1> ;
SHOWDDL <table2> ;
....
log ;
DROP SCHEMA <schema-name> CASCADE ;
CREATE SCHEMA <schema-name> ;
OBEY ddl.txt ;
LOAD INTO <table1> .... ;
LOAD INTO <table2> .... ;
Thank you
Suresh
On Tue, Mar 7, 2017 at 7:47 AM, Pierre Smits <pi...@gmail.com> wrote:
> Hi all,
>
> Is there in Trafodion an equivalent for dumping a schema, that consist of:
>
> - dropping the table(s) on exist,
> - creating the table(s) and index(es),
> - setting the permissions,
> - loading the data
>
> Best regards,
>
> Pierre Smits
>
> ORRTIZ.COM <http://www.orrtiz.com>
> OFBiz based solutions & services
>
> OFBiz Extensions Marketplace
> http://oem.ofbizci.net/oci-2/
>