Reading from object storage

Within the kdb Insights Database, the Data Access service can be configured to read directly from object storage. This mode allows a kdb+ partitioned database to be read from a static location in object storage. This is useful for easily querying existing kdb data that is managed outside of kdb Insights.

Tutorial

Query a kdb+ partitioned database from object storage

This example will deploy a kdb Insights Database that reads static data from an AWS S3 bucket. The same process works for Azure blob storage and Google Cloud Storage. Define the appropriate authentication parameters based on the selected cloud vendor.

Uploading your database to S3

To upload a database to S3, use aws s3 cp. You can skip this section if you already have a kdb+ partitioned database in a object storage.

In our example, we will create a simple data set with randomized data.

// Generate data for today and the past few days last few days
n:1000000;
d:asc .z.d - til 3;
{[d;n]sv[`;.Q.par[`:data/db/;d;`trade],`]set .Q.en[`:data/;([]sym:`$'n?.Q.A;time:("p"$d)+til n;price:n?100f;size:n?50f)];}[;n] each d;

Now we will upload this example data.

aws s3 cp --recursive "data" "s3://insights-example-data/"

The sym file above top of the database directory is an enumeration of all symbols in the table. Under the trade table, there is a sym column which will reference the top-level file with the indices of relevant symbol names.

data
├── db
│   ├── 2023.05.09
│   │   └── trade
│   │       ├── price
│   │       ├── size
│   │       ├── sym
│   │       └── time
│   ├── 2023.05.10
│   │   └── trade
│   │       ├── price
│   │       ├── size
│   │       ├── sym
│   │       └── time
│   └── 2023.05.11
│       └── trade
│           ├── price
│           ├── size
│           ├── sym
│           └── time
└── sym

Additionally, a par.txt needs to be added in a different location than the database. In this example, the par.txt file will contain the following content.

s3://insights-example-data/data/db

aws s3 cp par.txt s3://insights-example-data/data/par.txt

This par.txt is used below when mounting the database.

(Optional) Creating a service account

If you are running in Kubernetes, you can use a service account to allow read access to AWS S3 without using environment variables. This can be done using eksctl.

eksctl create iamserviceaccount --name kx-s3-read-access \
 --namespace <your namespace> \
 --region <your region> \
 --cluster <your cluster> \
 --attach-policy-arn arn:aws:iam::aws:policy/AmazonS3ReadOnlyAccess \
 --approve

Deploying

The static HDB uses a segmented database that points to the object storage bucket which is configured with a par.txt file. Additionally, to maintain performance with symbol queries, we need to directly mount the sym file into the database.

The configuration to drive the object storage tier uses a microservice assembly. This assembly has a single object mount with an explicit sym and par configuration on the DAP instance which point to the sym and par.txt generated previously.

name: odb
labels:
 name: odb-example
 type: odb
tables:
 trade:
 type: partitioned
 prtnCol: time
 columns:
 - name: time
 type: timestamp
 - name: sym
 type: symbol
 - name: price
 type: float
 - name: size
 type: float
mounts:
 odb:
 type: object
 baseURI: file:///data/db/odb
 partition: none
elements:
 dap:
 instances:
 odb:
 mountName: odb
 sym: s3://insights-example-data/data/sym
 par: s3://insights-example-data/data/par.txt

services:
 dap:
 image: ${kxi_da}
 env_file: .env
 environment:
 - KXI_SC=odb
 - KXI_ASSEMBLY_FILE=/data/asm.yaml
 - AWS_REGION=${AWS_REGION}
 - AWS_ACCESS_KEY_ID=${AWS_ACCESS_KEY_ID}
 - AWS_SECRET_ACCESS_KEY=${AWS_SECRET_ACCESS_KEY}
 - KDB_LICENSE_B64=${KDB_LICENSE_B64}
 command: ["-p", "5080"]
 ports:
 - 5080:5080
 volumes:
 - ./odb:/data/db/odb
 - ./asm.yaml:/data/asm.yaml

docker compose up

---
apiVersion: v1
kind: PersistentVolumeClaim
metadata:
 name: odb-pvc
spec:
 accessModes:
 - ReadWriteOnce
 volumeMode: Filesystem
 resources:
 requests:
 storage: 10Gi
---
apiVersion: v1
kind: ConfigMap
metadata:
 name: odb-cm
data:
 asm.yaml: |
 name: odb
 labels:
 name: odb-example
 type: odb
 tables:
 trade:
 type: partitioned
 prtnCol: time
 columns:
 - name: time
 type: timestamp
 - name: sym
 type: symbol
 - name: price
 type: float
 - name: size
 type: float
 mounts:
 odb:
 type: object
 baseURI: file:///data/db/odb
 partition: none
 elements:
 dap:
 instances:
 odb:
 mountName: odb
 sym: s3://insights-example-data/data/sym
 par: s3://insights-example-data/data/par.txt
---
apiVersion: v1
kind: Pod
metadata:
 name: odb
spec:
 containers:
 - name: odb
 image: portal.dl.kx.com/kxi-da:1.5.0
 ports:
 - containerPort: 5080
 env:
 - name: KXI_SC
 value: odb
 - name: KXI_ASSEMBLY_FILE
 value: /cfg/asm.yaml
 - name: AWS_REGION
 value: "us-east-2"
 - name: AWS_ACCESS_KEY_ID
 value: "[redacted]"
 - name: AWS_SECRET_ACCESS_KEY
 value: "[redacted]"
 - name: KDB_LICENSE_B64
 value: "[redacted]"
 volumeMounts:
 - name: config
 mountPath: /cfg
 - name: data
 mountPath: /data/db/odb
 args: ["-p", "5080"]
 volumes:
 - name: config
 configMap:
 name: odb-cm
 - name: data
 persistentVolumeClaim:
 claimName: odb-pvc
 securityContext:
 fsGroup: 65535

kubectl apply -f deploy.yaml