Fluidshell sqlimport with create table?

by Nov 4, 2015

The potential for the fluidshell sql import/export tools are amazing, but I find myself wanting to duplicate tables or create ad-hoc tables frequently. An ad-hoc query plus a sqlexport/sqlimport is a good start, but moving data to a new table requires that the new table be already created. One use case for this might be to move ad-hoc data from a transactional DBMS to an analytical DBMS e.g. ASE to IQ (which would be in another fluidshell session). 

   select * from mydb..mytable where employee_id = 1001

   sqlexport -f csv | sqlimport -f csv -k other-session newtable
 
The above will fail if newtable doesn’t exist. Apologies if syntax is slightly off.

 

So, short version — is there a way to create table with sqlimport, and is this a valid feature to request if not? I imagine there would be a new command line option specifying if the destination table should be created using the closest possible match from the source table. Between database versions/products the column types would vary, but in most cases a direct match could be made if not a close one. 

Big fan of ADS, thanks for a great tool. DP

Response

Niels Gron over 7 years ago
You can try different things based on your scenario. First thing to point out is that you can sqlexport to format INSERT and include an option to include the CREATE statement on a target table like this :

sqlexport -f INSERT -CT true -TT public.”Customer”
Here is an example output :

C:\Users\niels$:1> select * from public.”Customer” where false
C:\Users\niels$:2> sqlexport -f INSERT -CT true -TT “Customer”
CREATE TABLE public.Customer (
CustomerId int4 NOT NULL,
FirstName varchar(40) NOT NULL,
LastName varchar(20) NOT NULL,
Company varchar(80) NULL,
Address varchar(70) NULL,
City varchar(40) NULL,
State varchar(40) NULL,
Country varchar(40) NULL,
PostalCode varchar(10) NULL,
Phone varchar(24) NULL,
Fax varchar(24) NULL,
Email varchar(60) NOT NULL,
SupportRepIdint4 NULL
)
GO
This works ok as long as your ad-hoc query matches the table definition. What you currently can’t do is generate a CREATE TABLE statement or recreate a table based on a Resultset. So, if you have an adhoc query which has JOINs and the columns are a subset of multiple tables, then there is no way to generate a CREATE TABLE statement.

Are you trying to auto-generate a CREATE TABLE statement on a Resultset?
Are you trying to auto-generate a CREATE TABLE for a database which is different then the source Resultset? (eg Sybase->Oracle)

Daniel Packer over 7 years ago
Hi Niels, thanks for a quick and fantastic answer. I do have some responses — I hope that starting a new answer item is the appropriate way to reply.

The ability for sqlexport to produce inserts and create table is definitely helpful. I am looking for a unix-style command line where I can pipe sqlexport into sqlimport to generate the DDL/DML and create the table and populate it, possibly in another session (e.g. another server and possibly database product such as ASE to IQ).

The only way I can see to consume the output of sqlexport -f INSERT is to pipe to a file and source the file in a separate command. However, if I want to import the table on a different session/server, I need to also switch to that session.

To answer your questions — yes, I sometimes want to create CREATE TABLE and INSERT statements based on an ad-hoc query that may not match the source table(s) (so based on a result set), and sometimes I want the destination to be another session/server/product.

One partial work around is to select the ad-hoc query into a temp table, then sqlexport from the temp table, switch sessions, and source the create/import file from the sqlexport. This is a rather involved workflow that would be awesome as a single unix-style command line using a combinatino of sqlexport -f INSERT with a version of sqlimport that supports the INSERT format. Currently it doesn’t seem to support it. Thanks! DP

PS. I know what I’m asking for is difficult as it involves the ability to work at the level of result sets in the input and output levels of the command line, and that it’s potentially out of scope of what you’re trying to provide, but I’m just throwing the idea out there as it would make query data as flexible as files on UNIX. ADS packs an impossible number of helpful tools into one package, and I thank you for creating a tool that is a pleasure to use.

Niels Gron over 7 years ago
Generating an accurate DDL is changing because the driver meta data on the resultset isn’t always complete enough to generate an accurate DDL. Converting the DDL to anther database makes it doubly complicated. We currently don’t support this, but users have requested this type of functionality so it is on our radar.

Daniel Packer over 7 years ago
Generating an accurate DDL is changing because the driver meta data on the resultset isn’t always complete enough to generate an accurate DDL. Converting the DDL to anther database makes it doubly complicated. We currently don’t support this, but users have requested this type of functionality so it is on our radar.

Understood. Working with result sets and the driver meta data is going to be tough, however if you implemented either a session parameter to the “source” command, or a “-f INSERT” (SQL format) parameter to sqlimport, it would enable building simple single-command pipelines for import/export between data sources, even if limited to the same SQL dialect (which would mostly work in the case of say, Sybase ASE vs Sybase IQ). Thanks for the feedback and keep up the great work.

PS. Anyone working on combining result sets/tables/resources from multiple data sources in a single query? I suspect if result sets could be normalized as per the ad-hoc import/export discussion, it could lead to being able to do that. I have use of a tool that can do this, and it’s sometimes very handy (e.g. query Oracle, Sybase, and other products in a single report).

DP

Niels Gron over 7 years ago
>> a session parameter to the “source” command,

The source command just feeds the file into the shell buffer to be interpreted. So adding a session command doesn’t relate. But you can always create a script which does what you want, which is to change the session and then execute the import. You can then source that script file which will execute all the commands you want at once.

>> or a “-f INSERT” (SQL format) parameter to sqlimport

The sqlimport needs the full SQL script of INSERT statements to parse it up into batch statements before it can execute any of them. That is why you can’t pipe it into sqlimport. The solution is to have a universal data format which can be streamed, instead of using CSV or SQL Script, since both are limited. This is the solution we will need to evaluate for the future.

>> Anyone working on combining result sets/tables/resources from multiple data sources in a single query?

Yes. We were hoping to have this for 18.0, but not sure if it will make it in.