How to import a create table syntax of sql in kdb.

Community Support
1

Hi All,

 I am new in KDB. 

 I would like to import a table structure from sql to kdb.

The problem here is the table has approx 1600 columns (sounds crazy) but that’s true.

The sql syntax in .sql file something like below.

CREATE TABLE table_name (

    column1 datatype,

    column2 datatype,

    column3 datatype,

    //something 1600 columns here

    column1600 datatype,

);

is there any easy way to import/transform this syntax in kdb+. 

Any lead/script is highly appreciated.

Thanks and Regards,

S Pratap

2

hi, i guess you should use csv file…

3

Hi S Pratap,

If you change your ‘.sql’ file extension to ‘.s’ that contains, for example:

~ $ more t.sCREATE TABLE table_name ( column1 int, column2 varchar, column3 float, column1600 varchar )

You can load the file in a q session as follows:

q)\l sqltable.stable_nameq)meta table_namec | t f a----------| -----column1 | icolumn2 | scolumn3 | fcolumn1600| s`

This will automatically translate your sql table in to a kdb format.

For this to work, you must have s.k from kx in the $QHOME directory (where q is installed). This is usually installed with 32-bit kdb+ downloads but can also be found here: https://github.com/KxSystems/kdb/blob/master/s.k

Does this answer your question?

Regards,

Cameron McKee

4

Hi Cameron McKee,

  Thanks for the Swift reply.

  I tried with the solution, but it didn’t help. 

  Tried with a simple table.

  here is my table.s file that contains.

 CREATE table Persons
(
PersonID int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);

As mentioned s.k file is in $QHOME directory.

When tried to load the .s file –> q) \l table.s

getting below error thrown from s.k file.

q)
q)\l table.s
Persons " " 'PersonID   [2]  c:\q\s.k:22: .s.e:k){$[~#r::v'r@:&~(\_r::w x)in$asbyasctemporaryouterjoinon;;6>si[g]r j::0;.s[$_3#$*r]j;-6!c r j]}
                                                                                                                         ^
 .s )

The error is thrown from line num 22 of s.k file.  Difficult to understand whats the actual issue here.

Thanks and Regards,

 S Pratap

5

Hi Cameron McKee,

  Thanks for the Swift reply.

  I tried with the solution, but it didn’t help. 

  Tried with a simple table.

  here is my table.s file that contains.

 CREATE table Persons
(
PersonID int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);

As mentioned s.k file is in $QHOME directory.

When tried to load the .s file –> q) \l table.s

getting below error thrown from s.k file.

q)
q)\l table.s
Persons " " 'PersonID   [2]  c:\q\s.k:22: .s.e:k){$[~#r::v'r@:&~(\_r::w x)in$asbyasctemporaryouterjoinon;;6>si[g]r j::0;.s[$_3#$*r]j;-6!c r j]}
                                                                                                                         ^
 .s )

The error is thrown from line num 22 of s.k file.  Difficult to understand whats the actual issue here.

Thanks and Regards,

 S Pratap

6

Hi All,

The issue got resolved with just a hit and trial method.

Hope this may help others.

In the table.s file the content was separated by Enter .i.e new line.

CREATE table Persons
(
PersonID int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);  

When I alined it into a single line like below it worked.

CREATE table Persons( PersonID int,LastName varchar(255),FirstName varchar(255),Address varchar(255),City varchar(255));  // This works.

Thanks all.

Regards,

S Pratap

7

Hi S. Pratap,

In addition to your solution, I have further information to help clarify your problem.

I was able to replicate your error using your table.s file:

q)\l table.sPersons" "'PersonID [2] C:\q\s.k:22: .s.e:k){$[~#r::v’r@:&~(_r::w x)in$asbyasctemporaryouterjoinon;;6>si[g]r j::0;.s[$_3#$*r]j;-6!c r j]} ^ .s )

The issue here is that your table requires the first line “CREATE table Persons” to have no spaces before it, and for each subsequent line to have a space at the start of the line. For example:

CREATE table Persons ( PersonID int, LastName varchar(255), FirstName varchar(255), Address varchar(255), City varchar(255) );

This should allow the table to be loaded successfully:

q)\l table.sPersonsq)meta Personsc | t f a---------| -----PersonID | iLastName | sFirstName| sAddress | sCity | s`

The reason this is required is that q interprets subsequent lines as part of the same statement when there is at least one space at the start of each of the new lines.

I hope this also solves your issue.

Regards,

Cameron

8

Thanks a lot, Cameron.
It works.

Regards,

S Pratap