How to create a database with 2 tables of different length or multiple symbol columns?

Question

I have two tables:

• table1 spanning from 2017.01.01-> 2018.01.01
• table2 spanning from 2017.12.01 -> 2023.01.15 (containing mostly fake simulated data).

I want to create a historical database partitioned by date, and give the user the ability to append other data to it (I am not worried about duplicates for subsequent appends)

table2 also have two symbol columns (GBP and UK -> same for all entries -> might change when I get my hands on the real data).

I want to create a partitioned table to look like this:

HDB_DATE1_TAB1
  |      |_TAB2
  |_DATE2_TAB1
  |      |_TAB2
  |_DATE3_TAB1
  |_DATE4_TAB1
  |      |_TAB2
  |_DATE5_TAB1
  |_DATE6_TAB1
  |      |_TAB2
  .
  . 
  .

As you can see one of the tables has more entries than the other (significantly more). How can I achieve this structure in KDB?

At the moment I dropped the two symbol columns from table2. I saved the first one with .Q.dpft and the second one just as a normal splayed table. When I load the database just one table (table2) appears and I cannot query it in any way. Can you point me in the right direction?

Answer 1

The issue may be with your hdb structure described above, it's required that the tables be defined in each partition. The structure can be fixed with .Q.chk. I created a hdb following your steps above, trades was saved via .Q.dpft and quotes via set to create a splay.

tree -d db
db
├── 2007.07.25
│   ├── quotes
│   └── trades
└── 2007.07.26
    └── trades

Loading the db into a q session only returns the trade table.

q)\l db
q)\a
,`trades

Using .Q.chk will fill the missing tables in each partition.

q).Q.chk[`:.]
,`:./2007.07.26
()
q)\l .
q)\a
`quotes`trades
q)

Which can then be verified with tree:

tree -d db
db
├── 2007.07.25
│   ├── quotes
│   └── trades
└── 2007.07.26
    ├── quotes
    └── trades

Answer 2

That's not how the structure will look on disk, but have a look at .Q.bv[] to see if it will do what you need.

http://code.kx.com/q/ref/dotq/#partitioned-database-state