Thread: Simple table creation
Hello,
I need a way to quickly import many large tables from various ODBC data sources into PostgreSQL for reporting.
Ideally I am looking for a simple way to create all the tables to import to, but a tool to take the source and do the whole job would be just as appealing.
Cheers
Craig
I need a way to quickly import many large tables from various ODBC data sources into PostgreSQL for reporting.
Ideally I am looking for a simple way to create all the tables to import to, but a tool to take the source and do the whole job would be just as appealing.
Cheers
Craig
On Wed, Feb 23, 2011 at 1:52 AM, Craig Barnes <cjbarnes18@gmail.com> wrote:
Hello,
I need a way to quickly import many large tables from various ODBC data sources into PostgreSQL for reporting.
Ideally I am looking for a simple way to create all the tables to import to, but a tool to take the source and do the whole job would be just as appealing.
Cheers
Craig
Could please elaborate this use case?
--
Chetan Sutrave
Senior Software Engineer
EnterpriseDB Corporation
The Enterprise PostgreSQL Company
Phone: +91.20.30589523
Website: www.enterprisedb.com
EnterpriseDB Blog: http://blogs.enterprisedb.com/
Follow us on Twitter: http://www.twitter.com/enterprisedb
This e-mail message (and any attachment) is intended for the use of the individual or entity to whom it is addressed. This message contains information from EnterpriseDB Corporation that may be privileged, confidential, or exempt from disclosure under applicable law. If you are not the intended recipient or authorized to receive this for the intended recipient, any use, dissemination, distribution, retention, archiving, or copying of this communication is strictly prohibited. If you have received this e-mail in error, please notify the sender immediately by reply e-mail and delete this message.
Hi Chetan,
Thank you for replying.
I realise this is not a regular use case, but here is my situation.
I am taking over a reporting role in a company that is currently using a COBOL based ERP system. Due to the nature of the system, data extract is very slow, so overnight extracts are used for day to day reporting purposes, these extracts are stored as dbase files.
Other dbase files containing meta data have also been created to aid reporting.
This situation has led to many dbase files being used with many mashup combinations created for different report use cases, this is proving to be quite a maintenace overhead.
My preference is to dump all of the raw data into Postgress where I can create the needed indexes and views needed for all of the reports, then the only data that needs updating is the source data, not a myriad of dbase files.
Unfortunately the raw data consists of many tables with greater than 100 columns, given the time constraints I am faced with I cannott commit the time needed to create the tables in postgress manually.
I have python scripts that I am gradually adopting to incrementaly replicate the data once the structure is in place.
I have had some limited results using LibreOffice Base. I create a database connected to my postgress database and another connected to the COBOL ODBC connetor, then drag and drop tables from one to the other. There are problems with the detection of some data types, and once corrected it is very slow and raises an error at about 4 Million rows leaving the data in an unknown state
I would love to find a way to do this without error, it would save me so much time.
Hope you can help.
Once I have the current situation under control I plan to introduce the business to the Kimball approach.
Cheers
Craig
Thank you for replying.
I realise this is not a regular use case, but here is my situation.
I am taking over a reporting role in a company that is currently using a COBOL based ERP system. Due to the nature of the system, data extract is very slow, so overnight extracts are used for day to day reporting purposes, these extracts are stored as dbase files.
Other dbase files containing meta data have also been created to aid reporting.
This situation has led to many dbase files being used with many mashup combinations created for different report use cases, this is proving to be quite a maintenace overhead.
My preference is to dump all of the raw data into Postgress where I can create the needed indexes and views needed for all of the reports, then the only data that needs updating is the source data, not a myriad of dbase files.
Unfortunately the raw data consists of many tables with greater than 100 columns, given the time constraints I am faced with I cannott commit the time needed to create the tables in postgress manually.
I have python scripts that I am gradually adopting to incrementaly replicate the data once the structure is in place.
I have had some limited results using LibreOffice Base. I create a database connected to my postgress database and another connected to the COBOL ODBC connetor, then drag and drop tables from one to the other. There are problems with the detection of some data types, and once corrected it is very slow and raises an error at about 4 Million rows leaving the data in an unknown state
I would love to find a way to do this without error, it would save me so much time.
Hope you can help.
Once I have the current situation under control I plan to introduce the business to the Kimball approach.
Cheers
Craig
On 7 March 2011 12:47, Chetan Suttraway <chetan.suttraway@enterprisedb.com> wrote:
Could please elaborate this use case?On Wed, Feb 23, 2011 at 1:52 AM, Craig Barnes <cjbarnes18@gmail.com> wrote:Hello,
I need a way to quickly import many large tables from various ODBC data sources into PostgreSQL for reporting.
Ideally I am looking for a simple way to create all the tables to import to, but a tool to take the source and do the whole job would be just as appealing.
Cheers
Craig
--
Chetan Sutrave
Senior Software Engineer
EnterpriseDB Corporation
The Enterprise PostgreSQL Company
Phone: +91.20.30589523
Website: www.enterprisedb.com
EnterpriseDB Blog: http://blogs.enterprisedb.com/
Follow us on Twitter: http://www.twitter.com/enterprisedb
This e-mail message (and any attachment) is intended for the use of the individual or entity to whom it is addressed. This message contains information from EnterpriseDB Corporation that may be privileged, confidential, or exempt from disclosure under applicable law. If you are not the intended recipient or authorized to receive this for the intended recipient, any use, dissemination, distribution, retention, archiving, or copying of this communication is strictly prohibited. If you have received this e-mail in error, please notify the sender immediately by reply e-mail and delete this message.
Craig,
1. ODBC is not fast by nature adding a layer on top of the direct access libraries. Can you dump from cobol to text delimited file (or fixed length) and then up into Postgres ?
2. when you say 'Raises an error': what error do you get ?
3. 4 millions rows seems like a fairly sizeable amount of data, have you checked you have enough space on the disk where the Postgres DB resides ?
4. Can't you do your upload piece by piece; I would have thought that out of the n millions rows, a large chunk are historical data which do not need to be exported on a daily basis.
Didier
1. ODBC is not fast by nature adding a layer on top of the direct access libraries. Can you dump from cobol to text delimited file (or fixed length) and then up into Postgres ?
2. when you say 'Raises an error': what error do you get ?
3. 4 millions rows seems like a fairly sizeable amount of data, have you checked you have enough space on the disk where the Postgres DB resides ?
4. Can't you do your upload piece by piece; I would have thought that out of the n millions rows, a large chunk are historical data which do not need to be exported on a daily basis.
Didier
Hi Didier
Thank you for ideas and suggestions, you have given me some new thngs to try, I will feedback how I get on.
Craig
On 8 March 2011 14:24, Didier Gasser-Morlay <didiergm@gmail.com> wrote:
The native reporting tool "Impromptu" uses the same ODBC link and transfers data at a significantly faster rate, Unfortunately it seems to have a file size cap of 4GB, although I've not yet tried a CSV output, will give that a go.
Yes, plenty of space, it's a new data only disk. I will monitor C:\ drive space on the next attempt in case its a temp space issue.
Craig,
1. ODBC is not fast by nature adding a layer on top of the direct access libraries. Can you dump from cobol to text delimited file (or fixed length) and then up into Postgres ?
The native reporting tool "Impromptu" uses the same ODBC link and transfers data at a significantly faster rate, Unfortunately it seems to have a file size cap of 4GB, although I've not yet tried a CSV output, will give that a go.
2. when you say 'Raises an error': what error do you get ?
The error unfortunately does not help much, it complains about not being able to access a file under /var/log/ but this itself is a bug on a widows machine, so I don't get to see the underlying error (I am working on a bug report).
3. 4 millions rows seems like a fairly sizeable amount of data, have you checked you have enough space on the disk where the Postgres DB resides ?
Yes, plenty of space, it's a new data only disk. I will monitor C:\ drive space on the next attempt in case its a temp space issue.
4. Can't you do your upload piece by piece; I would have thought that out of the n millions rows, a large chunk are historical data which do not need to be exported on a daily basis.
Indeed, I will only need incremental data following the initial dump for most tables, I am working on an incremental data dump Python script where time permits, this is how I intend to maintain the data.
It would be nice to get a good head start while I work on this, plus it will allow me to get some metrics to justify the time investment on the scripts.
Either way I have a way forward, Just would be handy if anyone knew of a good tool for the initial data dump.
It would be nice to get a good head start while I work on this, plus it will allow me to get some metrics to justify the time investment on the scripts.
Either way I have a way forward, Just would be handy if anyone knew of a good tool for the initial data dump.
Didier
Thank you for ideas and suggestions, you have given me some new thngs to try, I will feedback how I get on.
Craig