Database

When loading an existing database or creating a database, you can specify dbName to indicate the handle name after the database is loaded into memory. If this parameter is not specified, a random string will be automatically generated as the handle name, which can be obtained through the _getDbName() method.

Example 1. Creating database without dbName

dbPath = "dfs://dbName"
if s.existsDatabase(dbPath):
    s.dropDatabase(dbPath)
db = s.database(partitionType=keys.VALUE, partitions=[1, 2, 3], dbPath=dbPath)

dbName = db._getDbName()
print(dbName)
print(s.run(dbName))

Output:

TMP_DB_15c2bf85DB
DB[dfs://dbName]

Example 2. Creating database with dbName

dbPath = "dfs://dbName"
if s.existsDatabase(dbPath):
    s.dropDatabase(dbPath)
db = s.database(dbName="testDB", partitionType=keys.VALUE, partitions=[1, 2, 3], dbPath=dbPath)

dbName = db._getDbName()
print(dbName)
print(s.run(dbName))

Output:

testDB
DB[dfs://dbName]

When creating database with Session.database, partitionType and partitions must be specified. When creating a DFS database, dbPath must be specified; otherwise, it is not required.

By default, database creates OLAP databases. You can specify engine to create databases with other types of storage engines.

atomic indicates at which level the atomicity is guaranteed for a write transaction, thus determining whether concurrent writes to the same chunk are allowed. It can be ‘TRANS’ (default) or ‘CHUNK’.

• ‘TRANS’ indicates that the atomicity is guaranteed at the transaction level. If a transaction attempts to write to multiple chunks and one of the chunks is locked by another transaction, a write-write conflict occurs, and all writes of the transaction fail. Therefore, setting atomic =’TRANS’ means concurrent writes to a chunk are not allowed.
• ‘CHUNK’ indicates that the atomicity is guaranteed at the chunk level. If a transaction tries to write to multiple chunks and a write-write conflict occurs as a chunk is locked by another transaction, instead of aborting the writes, the transaction will keep writing to the non-locked chunks and keep attempting to write to the chunk in conflict until it is still locked after a few minutes. Therefore, setting atomic =’CHUNK’ means concurrent writes to a chunk are allowed. As the atomicity at the transaction level is not guaranteed, the write operation may succeed in some chunks but fail in other chunks. Please also note that the write speed may be impacted by the repeated attempts to write to the chunks that are locked.

chunkGranularity determines whether concurrent writes to different tables in the same chunk are allowed. The value can be "TABLE" or "DATABASE":

• "TABLE": the chunk granularity is at the TABLE level. In this case, concurrent writes to different tables in the same partition are allowed.
• "DATABASE": the chunk granularity is at the DATABASE level. In this case, concurrent writes to different tables in the same partition are not allowed.

Note: This parameter is only enabled when the server configuration parameter enableChunkGranularityConfig is set to true.

The following example creates a dimension table "dt" which uses the schema of the "schema_t" table and sorts data based on the "csymbol" column.

dbPath = "dfs://createTable"
if s.existsDatabase(dbPath):
    s.dropDatabase(dbPath)
db = s.database(partitionType=keys.VALUE, partitions=[1, 2, 3], dbPath=dbPath, engine="TSDB")
s.run("schema_t = table(100:0, `ctime`csymbol`price`qty, [TIMESTAMP, SYMBOL, DOUBLE, INT])")
schema_t = s.table(data="schema_t")
dt = db.createTable(schema_t, "dt", ["csymbol"])
schema = s.run(f'schema(loadTable("{dbPath}", "dt"))')
print(schema["colDefs"])

Output:

       name typeString  typeInt  extra comment
0    ctime  TIMESTAMP       12    NaN        
1  csymbol     SYMBOL       17    NaN        
2    price     DOUBLE       16    NaN        
3      qty        INT        4    NaN   

The following example creates a dimension table pt based on "schema_t" in a PKEY database. The "csymbol" column is specified as the primary key. The indexes parameter is not specified here, so by default, only the primary key column "csymbol" uses a bloomfilter indexing, while all other columns use zonemap indexing.

dbPath = "dfs://createTable"
if s.existsDatabase(dbPath):
    s.dropDatabase(dbPath)
db = s.database(partitionType=keys.VALUE, partitions=[1, 2, 3], dbPath=dbPath, engine="PKEY")
s.run("schema_t = table(100:0, `ctime`csymbol`price`qty, [TIMESTAMP, SYMBOL, DOUBLE, INT])")
schema_t = s.table(data="schema_t")
pt = db.createTable(schema_t, "pt", primaryKey=["csymbol"])
schema = s.run(f'schema(loadTable("{dbPath}", "pt"))')
print(schema["primaryKey"])
print(schema["indexes"])

Output:

['csymbol']
                       name         type columnName
0             ctime_zonemap      zonemap      ctime
1  _primary_key_bloomfilter  bloomfilter    csymbol
2           csymbol_zonemap      zonemap    csymbol
3               qty_zonemap      zonemap        qty

The following example creates a dimension table pt based on "schema_t" in a PKEY database. The "csymbol" and "qty" columns are specified as the primary key columns. The composite key and the "qty" column use bloomfilter indexing, while all other columns use zonemap indexing.

dbPath = "dfs://createTable"
if s.existsDatabase(dbPath):
    s.dropDatabase(dbPath)
db = s.database(partitionType=keys.VALUE, partitions=[1, 2, 3], dbPath=dbPath, engine="PKEY")
s.run("schema_t = table(100:0, `ctime`csymbol`price`qty, [TIMESTAMP, SYMBOL, DOUBLE, INT])")
schema_t = s.table(data="schema_t")
pt = db.createTable(schema_t, "pt", primaryKey=["csymbol", "qty"], indexes={"qty": ["bloomfilter"]})
schema = s.run(f'schema(loadTable("{dbPath}", "pt"))')
print(schema["primaryKey"])
print(schema["indexes"])

Output:

['csymbol' 'qty']
                       name         type              columnName
0  _primary_key_bloomfilter  bloomfilter  _composite_primary_key
1             ctime_zonemap      zonemap                   ctime
2           csymbol_zonemap      zonemap                 csymbol
3               qty_zonemap      zonemap                     qty
4           qty_bloomfilter  bloomfilter                     qty

This example creates a partitioned table with partitioning column "TradeDate" and sort columns ["SecurityID", "TradeDate"] in a TSDB database. The schema of the partitioned table is extracted from the "schema_t" table. It specifies the sorting columns as SecurityID and TradeDate, where the sorting function of SecurityID uses hashBucket {, 5}, and the handling strategy when the sorting column values are the same is "ALL".

The following example creates a partitioned table pt in a TSDB database. pt uses the schema of the "schema_t" table with partitioning column "TradeDate" and sort columns "SecurityID" and "TradeDate". The function hashBucket{,5} is applied to the "TradeDate" column for dimensionality reduction. With duplicate sort columns values, it keeps all records.

dbPath = "dfs://createPartitionedTable"
if s.existsDatabase(dbPath):
    s.dropDatabase(dbPath)
dates = np.array(pd.date_range(start='20220101', end='20220105'), dtype="datetime64[D]")
db = s.database(partitionType=keys.VALUE, partitions=dates, dbPath=dbPath, engine="TSDB")
s.run("schema_t = table(100:0, `SecurityID`TradeDate`TotalVolumeTrade`TotalValueTrade, [SYMBOL, DATE, INT, DOUBLE])")
schema_t = s.table(data="schema_t")
pt = db.createPartitionedTable(schema_t, "pt", partitionColumns="TradeDate", sortColumns=["SecurityID", "TradeDate"], keepDuplicates="ALL", sortKeyMappingFunction=["hashBucket{,5}"])
schema = s.run(f'schema(loadTable("{dbPath}", "pt"))')
print(schema["colDefs"])

Output:

                name typeString  typeInt  extra comment
0        SecurityID     SYMBOL       17    NaN        
1         TradeDate       DATE        6    NaN        
2  TotalVolumeTrade        INT        4    NaN        
3   TotalValueTrade     DOUBLE       16    NaN

The following example creates a partitioned table pt in an OLAP database. pt uses the schema of the "schema_t" table. The partitioning column is the "symbol" column and the "timestamp" column is compressed using the "delta" method.

dbPath = "dfs://createPartitionedTable"
if s.existsDatabase(dbPath):
    s.dropDatabase(dbPath)
db = s.database(partitionType=keys.VALUE, partitions=["IBM", "MS"], dbPath=dbPath)
s.run("schema_t = table(100:0, `timestamp`symbol`value, [TIMESTAMP, SYMBOL, DOUBLE])")
schema_t = s.table(data="schema_t")
pt = db.createPartitionedTable(schema_t, "pt", partitionColumns="symbol", compressMethods={'timestamp': "delta"})
schema = s.run(f'schema(loadTable("{dbPath}", "pt"))')
print(schema["colDefs"])

Output:

        name typeString  typeInt  extra comment
0  timestamp  TIMESTAMP       12    NaN        
1     symbol     SYMBOL       17    NaN        
2      value     DOUBLE       16    NaN  

The following example creates a partitioned DFS table pt based on "schema_t" in a PKEY database. The "SecurityID" and "TradeDate" columns are specified as the primary key columns. The partitioning column is "TradeDate". The primary key column "SecurityID" uses bloomfilter indexing.

dbPath = "dfs://createPartitionedTable"
if s.existsDatabase(dbPath):
    s.dropDatabase(dbPath)
dates = np.array(pd.date_range(start='20220101', end='20220105'), dtype="datetime64[D]")
db = s.database(partitionType=keys.VALUE, partitions=dates, dbPath=dbPath, engine="PKEY")
s.run("schema_t = table(100:0, `SecurityID`TradeDate`TotalVolumeTrade`TotalValueTrade, [SYMBOL, DATE, INT, DOUBLE])")
schema_t = s.table(data="schema_t")
pt = db.createPartitionedTable(schema_t, "pt", partitionColumns="TradeDate", primaryKey=["SecurityID", "TradeDate"], indexes={'SecurityID': ["bloomfilter"]})
schema = s.run(f'schema(loadTable("{dbPath}", "pt"))')
print(schema["primaryKey"])
print(schema["indexes"])

Output:

['SecurityID' 'TradeDate']
                       name         type              columnName
0  _primary_key_bloomfilter  bloomfilter  _composite_primary_key
1        SecurityID_zonemap      zonemap              SecurityID
2    SecurityID_bloomfilter  bloomfilter              SecurityID
3  TotalVolumeTrade_zonemap      zonemap        TotalVolumeTrade