Code Samples

Using Polybase database

To use the Polybase database you can either use the default one that the data programs team has created or you can choose to read from a different one. To use the default database you will need the following import.

import {DB} from "@dataprograms/repdao-polybase";

To read from a different database you will have to add the below where NAME_SPACE is the name of the space you want to use.

import { Polybase } from "@polybase/client"
const db = new Polybase({ defaultNamespace: "NAME_SPACE" });

The following examples will use the default Polybase database provided. It should also be noted that results are paginated and the developer is responsible for paging through the results if 100 rows is not enough.

Code Samples

Filtering by single column value in single collection

Example is getting one row where the provider is f01889512 in the filfox collection. Currently filtering and sorting on multiple columns is not supported because you have to create an index on the columns to support multiple where statements. see doc.

import {DB, filfox} from "@dataprograms/repdao-polybase";

const provider = 'f01889512'

const doc = (await DB.collection('filfox')
    .where('provider', '==', provider)
    .limit(1).get())
    .data[0].data as filfox

console.log(`Filfox record for ${provider}: total rewards: ${doc.totalRewards}`)

You can however have multiple where statements on the same column name.

import {DB, filfox} from "@dataprograms/repdao-polybase";

const doc = (await DB.collection('filfox')
    .where('provider', '>', 'f01889512')
    .where('provider', '<=', 'f01889612')
    .limit(1).get())
    .data[0].data as filfox
    console.log(doc)

console.log(`Filfox record has total rewards: ${doc.totalRewards}`)

Iterating over all the provided collections

import {PolybaseError } from "@polybase/client"
import {DB, CollectionNames} from "@dataprograms/repdao-polybase";

for (const collectionName of CollectionNames) {
    let response
    try {
        response = await DB.collection(collectionName).limit(1).get()
        console.log(`Data for polybase collection ${collectionName}`)
        console.log(response.data[0].data)
    } catch (e: any) {
        if (e instanceof PolybaseError) {
            console.error(`Polybase error: ${e.code} ${e.message} when retrieving ${collectionName}.`)
            continue
        }
        throw e
    }
}

Sorting column to find max or min

This example finds the max epoch that is less than 2849899 in the filfox collection.

import {DB, filfox} from "@dataprograms/repdao-polybase";

const doc = (await DB.collection('filfox')
    .where('epoch', '<', 2849899)
    .sort('epoch', 'desc')
    .limit(1).get())
    .data[0].data as filfox
    console.log(doc)

console.log(`Filfox record: ${doc.provider}`)

Using sort and limit to find the top n

This example finds the 2 largest epoch values that are less than 2849899 in the filfox collection.

import {DB, filfox} from "@dataprograms/repdao-polybase";

const doc = (await DB.collection('filfox')
    .where('epoch', '<', 2849899)
    .sort('epoch', 'desc')
    .limit(2).get()) // 2 is n

    doc.data.forEach(e => {
        console.log(e.data as filfox )
    })

Finding rows X days ago

Each collection has a column called date_stamp that can be used to search for rows before, after or between given dates.

// This example won't work with the current private key 
// in the repo because date stamp was added after 

import {PolybaseError } from "@polybase/client"
import { DB, CollectionNames} from "@dataprograms/repdao-polybase";

for (const collectionName of CollectionNames) {
    let response
    try {
        let daysToLookBack = 2 //X
        response = await DB.collection(collectionName)
            .where("date_stamp", ">=", 
                (new Date(new Date().getTime() - (daysToLookBack * 24 * 60 * 60 * 1000)))
            .toISOString().substring(0,10)).sort("date_stamp", "asc").limit(2).get();
        console.log(`Data for polybase collection ${collectionName}`)
        response.data.forEach(element => {
            console.log(element.data)
        });
    } catch (e: any) {
        if (e instanceof PolybaseError) {
            console.error(`Polybase error: ${e.code} ${e.message} when retrieving ${collectionName}.`)
            continue
        }
        throw e
    }
}