Monthly Archives: April 2013

Creating a table in KDB using Q

Creating a table in KDB using Q

Creating and populating a simple keyed table with time series identifiers (sym), dates (date) and values (val)

[sourcecode language="kdb"]

q)TestTable : ([sym:`symbol$(); date:`date$()] val:`float$())

[/sourcecode]

The contents of the table will be displayed simply by typing the table name at the command prompt:

q> TestTable

And an empty table will be displayed, just the meta-data ie the column names with no rows shown:

sym date| val ——–| —

We can now insert our first value:

q> insert [`TestTable] (`GBPUSD; 2011.02.10; 1.5074)

On typing the table name, the contents are displayed in tabular format

q> TestTable

you will now see

sym    date      | val —————–| ——

EURUSD 2011.02.10| 1.5074

You can also

q> select * from TestTable

and this generates the same result:

sym    date      | val -----------------| ------

GBPUSD 2011.02.10| 1.5074

Is it possible to insert several rows at once?

Yes,  Note that there is no space in “`USDJPY`ARSRUB”.

q> insert [`TestTable] ([sym: `USDJPY`ARSRUB; date: 2009.02.10 2009.02.11]; val: 1.2874 10.252969)

The result of the previous insert statement is:

MySQL Using LOAD DATA INFILE

The LOAD DATA INFILE command is a flexible way to upload bulk volumes of data and is especially useful for loading data files which have been submitted in a csv format.

 

LOAD DATA LOCAL INFILE ‘file.csv’ INTO TABLE my_table
FIELDS TERMINATED BY ‘,’
ENCLOSED BY ‘”‘
LINES TERMINATED BY ‘\n’
(name, address, @var1)
set dateOfBirth = STR_TO_DATE(@var1, ‘%d-%b-%y’)

In the above code segment I use the SET clause, along with a variable to reference the contents of the row at that column. In the column list, I assign the date column to a variable name. I can then use it in the SET statement to provide the necessary date formatting rule for the data file in question.