Programming/Kdb/Labs/Big data in kdb - Revision history

Admin at 22:14, 21 December 2021

2021-12-21T22:14:48Z

← Older revision		Revision as of 22:14, 21 December 2021
Line 319:		Line 319:
	* the '''compression level''': 0-9 for gzip, 1-12 for lz4hc.		* the '''compression level''': 0-9 for gzip, 1-12 for lz4hc.

	To read up on file compression in kdb+/q, see ~~<span class="plainlinks">~~https://code.kx.com/q/kb/file-compression/~~</span>~~.		To read up on file compression in kdb+/q, see https://code.kx.com/q/kb/file-compression/.

	==Querying splayed tables on disc==		==Querying splayed tables on disc==
Line 699:		Line 699:
	</pre>		</pre>

	(We remark that this modification of <tt>.Q.dpft</tt> was suggested by Pawan Singh — see ~~<span class="plainlinks">~~https://groups.google.com/forum/\#!topic/personal-kdbplus/51rxAntpcOc~~</span>~~.)		(We remark that this modification of <tt>.Q.dpft</tt> was suggested by Pawan Singh — see https://groups.google.com/forum/\#!topic/personal-kdbplus/51rxAntpcOc.)

	This time we have achieved our goal of appending to a partition in <tt>qt</tt>: after restarting again with		This time we have achieved our goal of appending to a partition in <tt>qt</tt>: after restarting again with
Line 744:		Line 744:
	==Maintenance==		==Maintenance==

	For maintenance of partitioned tables, be sure to examine <tt>dbmaint.q</tt>: ~~<span class="plainlinks">~~https://github.com/KxSystems/kdb/blob/master/utils/dbmaint.md~~</span>~~:		For maintenance of partitioned tables, be sure to examine <tt>dbmaint.q</tt>: https://github.com/KxSystems/kdb/blob/master/utils/dbmaint.md:
	<blockquote>		<blockquote>
	The script <tt>dbmaint.q</tt> contains utility functions for maintenance of partitioned database tables in kdb+. It should not be used for splayed tables which are not partitioned, tables stored as single files or in memory tables.		The script <tt>dbmaint.q</tt> contains utility functions for maintenance of partitioned database tables in kdb+. It should not be used for splayed tables which are not partitioned, tables stored as single files or in memory tables.

Admin at 13:44, 2 July 2021

2021-07-02T13:44:25Z

← Older revision		Revision as of 13:44, 2 July 2021
Line 683:		Line 683:
	here we replace <tt>:</tt> with <tt>,</tt> and obtain		here we replace <tt>:</tt> with <tt>,</tt> and obtain
	<pre>		<pre>
	~~q).Q.dpft~~
	k)dpftappend:{[d;p;f;t;s]i:<t f;r:+.Q.enxs[$;d;;s]`. . `\:t;{[d;t;i;u;x]@[d;x;,;u t[x]i]}[d:.Q.par[d;p;t];r;i;]'[(::;`p#)f=!r;!r];@[d;`.d;:;f,r@&~f=r:!r];t}[;;;;`sym]		k)dpftappend:{[d;p;f;t;s]i:<t f;r:+.Q.enxs[$;d;;s]`. . `\:t;{[d;t;i;u;x]@[d;x;,;u t[x]i]}[d:.Q.par[d;p;t];r;i;]'[(::;`p#)f=!r;!r];@[d;`.d;:;f,r@&~f=r:!r];t}[;;;;`sym]
	</pre>		</pre>
Line 696:		Line 695:
	<pre>		<pre>
	qt:morequotes;		qt:morequotes;
	dpftappend[`$":C:/Users/Paul Bilokon/Documents/mydb";2016.04.07;`qt];		dpftappend[`$":C:/Users/Paul Bilokon/Documents/mydb";2016.04.07;`code;`qt];
	delete qt from`.;		delete qt from`.;
	</pre>		</pre>

Admin: /* Appending to a splayed table */

2021-07-01T17:01:40Z

Appending to a splayed table

← Older revision		Revision as of 17:01, 1 July 2021
Line 231:		Line 231:
	If we were to do		If we were to do
	<pre>		<pre>
	(`$":C:/Users/Paul Bilokon/Documents/tradesdir/")set .Q.en[`$":C:/Users/Paul Bilokon/Documents/~~tradesdir~~/";moretrades]		(`$":C:/Users/Paul Bilokon/Documents/data/tradesdir/")set .Q.en[`$":C:/Users/Paul Bilokon/Documents/data/";moretrades]
	</pre>		</pre>
	the table would be overwritten. However, we don't want to overwrite it; we want to append to it.		the table would be overwritten. However, we don't want to overwrite it; we want to append to it.
Line 237:		Line 237:
	What we want is		What we want is
	<pre>		<pre>
	upsert[`$":C:/Users/Paul Bilokon/Documents/tradesdir/"].Q.en[`$":C:/Users/Paul Bilokon/Documents/~~tradesdir~~/";moretrades]		upsert[`$":C:/Users/Paul Bilokon/Documents/data/tradesdir/"].Q.en[`$":C:/Users/Paul Bilokon/Documents/data/";moretrades]
	</pre>		</pre>

Admin: /* Enumerating the columns */

2021-07-01T16:47:24Z

Enumerating the columns

← Older revision		Revision as of 16:47, 1 July 2021
Line 110:		Line 110:
	We couldn't save the original <tt>trades</tt> table because the columns of type symbol, namely <tt>code</tt> and <tt>mktflag</tt>, weren't enumerated. We shall now show how to enumerate them. Before proceeding, delete the directory <tt>tradesdir</tt> with all its contents, in case you created that directory by following the steps outlined above, so that we start with a clean slate. To enumerate the symbol columns in <tt>trades</tt>, we use <tt>.Q.en</tt>:		We couldn't save the original <tt>trades</tt> table because the columns of type symbol, namely <tt>code</tt> and <tt>mktflag</tt>, weren't enumerated. We shall now show how to enumerate them. Before proceeding, delete the directory <tt>tradesdir</tt> with all its contents, in case you created that directory by following the steps outlined above, so that we start with a clean slate. To enumerate the symbol columns in <tt>trades</tt>, we use <tt>.Q.en</tt>:
	<pre>		<pre>
	tradesenum:.Q.en[`$":C:/Users/Paul Bilokon/Documents/~~tradesdir~~/";trades]		tradesenum:.Q.en[`$":C:/Users/Paul Bilokon/Documents/data/";trades]
	</pre>		</pre>
	This will create the directory <tt>~~tradesdir~~</tt> with a single binary file in it, named <tt>sym</tt>. That file enumerates the values found in the columns of type symbol, namely <tt>`CLM16</tt>, <tt>`GCM16</tt>, <tt>`VXK16</tt>, and <tt>`E</tt>.		This will create the directory <tt>data</tt> with a single binary file in it, named <tt>sym</tt>. That file enumerates the values found in the columns of type symbol, namely <tt>`CLM16</tt>, <tt>`GCM16</tt>, <tt>`VXK16</tt>, and <tt>`E</tt>.

	Once this is done, we run		Once this is done, we run
	<pre>		<pre>
	(`$":C:/Users/Paul Bilokon/Documents/tradesdir/")set tradesenum		(`$":C:/Users/Paul Bilokon/Documents/data/tradesdir/")set tradesenum
	</pre>		</pre>
	The directory now contains the files with names		The directory now contains the files with names
Line 126:		Line 126:
	* <font color="red"><tt>mktflag</tt></font>,		* <font color="red"><tt>mktflag</tt></font>,
	* <tt>price</tt>,		* <tt>price</tt>,
	* <font color="red"><tt>sym</tt></font>,
	* <tt>time</tt>, and		* <tt>time</tt>, and
	* <tt>volume</tt>.		* <tt>volume</tt>.
Line 133:		Line 132:
	Note that, unlike the above command,		Note that, unlike the above command,
	<pre>		<pre>
	(`$":C:/Users/Paul Bilokon/Documents/tradesdir/")set trades		(`$":C:/Users/Paul Bilokon/Documents/data/tradesdir/")set trades
	</pre>		</pre>
	will fail with <tt>'type</tt>, as before.		will fail with <tt>'type</tt>, as before.

Admin at 20:23, 20 June 2021

2021-06-20T20:23:26Z

@@ Line 751: / Line 751: @@
 </blockquote>
-In particular, this script defines the functions <tt>addcol</tt>, <tt>castcol</tt>, <tt>clearattrcol</tt>, <tt>copycol</tt>, <tt>deletecol</tt>, <tt>findcol</tt>, <tt>fixtable</tt>, <tt>fncol</tt>, <tt>listcols</tt>, <tt>renamecol</tt>, <tt>reordercols</tt>, <tt>setattrcol</tt>, <tt>addtable</tt>, <tt>rentable</tt>.
+In particular, this script defines the functions <tt>addcol</tt>, <tt>castcol</tt>, <tt>clearattrcol</tt>, <tt>copycol</tt>, <tt>deletecol</tt>, <tt>findcol</tt>, <tt>fixtable</tt>, <tt>fncol</tt>, <tt>listcols</tt>, <tt>renamecol</tt>, <tt>reordercols</tt>, <tt>setattrcol</tt>, <tt>addtable</tt>, <tt>rentable</tt>. See the documentation for details.
-See the documentation for details.
+Another useful function for working with partitioned tables is <tt>.Q.chk</tt> (fill historical database, hdb). It is run as follows:

Admin at 20:10, 20 June 2021

2021-06-20T20:10:04Z

@@ Line 443: / Line 443: @@
 Notice that we didn't have to call <tt>.Q.en</tt> explicitly this time: <tt>.Q.dpft</tt> did this for us.
 ==Adding another table==
@@ Line 700: / Line 743: @@
 .z.zd:3#0
 </pre>

Admin at 19:53, 20 June 2021

2021-06-20T19:53:29Z

@@ Line 309: / Line 309: @@
 Without compression the table occupied 17,595 bytes; with compression &mdash; only 1,182 bytes.
 To read up on file compression in kdb+/q, see <span class="plainlinks">https://code.kx.com/q/kb/file-compression/</span>.
@@ Line 674: / Line 684: @@
 .04.08 CLM16 15:20:02.000 40.77    26      40.79    44      E
 .04.08 CLM16 15:21:43.786 40.76    3       40.77    28      E
 </pre>

Admin at 19:29, 20 June 2021

2021-06-20T19:29:05Z

← Older revision		Revision as of 19:29, 20 June 2021
Line 68:		Line 68:

	=Splayed tables=		=Splayed tables=

			==Introduction==

	Instead of saving the table as a single binary file, we could save it '''splayed''', i.e. so that each column is saved in a separate binary file.		Instead of saving the table as a single binary file, we could save it '''splayed''', i.e. so that each column is saved in a separate binary file.
Line 332:		Line 334:

	=Partitioned tables=		=Partitioned tables=

			==Introduction==

	In addition to splaying a table, we may partition it: for each distinct value <tt>v</tt> of a column that defines the partition, a distinct directory will be created, where the binary files with serialized columns will be stored, but including only those rows with value <tt>v</tt> in the partition column.		In addition to splaying a table, we may partition it: for each distinct value <tt>v</tt> of a column that defines the partition, a distinct directory will be created, where the binary files with serialized columns will be stored, but including only those rows with value <tt>v</tt> in the partition column.

Admin at 19:04, 20 June 2021

2021-06-20T19:04:42Z

Show changes

Admin at 18:33, 20 June 2021

2021-06-20T18:33:01Z

← Older revision		Revision as of 18:33, 20 June 2021
Line 425:		Line 425:
	*** <tt>volume</tt>		*** <tt>volume</tt>
	* <tt>sym</tt>		* <tt>sym</tt>

			In the directory that we have specified to <tt>.Q.dpft</tt>, we have one subdirectory per each partition (<tt>2016.04.07</tt>, <tt>2016.04.08</tt>, and <tt>2016.04.11</tt>). Inside those directories we have a subdirectory whose name is that of the table that we provided to <tt>.Q.dpft</tt> as an argument: <tt>trd</tt>. Inside <tt>trd</tt>, we have the same files as we encountered when splaying the table, with the exception of <tt>sym</tt>, which now resides two levels above. As before, the file <tt>sym</tt> enumerates the columns of type <tt>symbol</tt>

			Notice that we didn't have to call <tt>.Q.en</tt> explicitly this time: <tt>.Q.dpft</tt> did this for us.