Getting Started

In DolphinDB, databases and tables can be created using functions database/table or the CREATE statement.

The following example uses SQL statements to create a database with composite partitioning on date and symbols as well as a table in that database based on the sample stock data (including “TradeTime”, “SecurityID”, “TotalVolumeTrade”, and “TotalValueTrade”).

First, use the CREATE statement to create an OLAP database named db. Enter the following script into the editor of the Web Interface and click Execute (shortcut: Ctrl+E).

CREATE DATABASE "dfs://db"
PARTITIONED BY VALUE(2020.01.01..2021.01.01), HASH([SYMBOL, 4])

Note: DolphinDB supports various partition types. A suitable partition type not only helps users evenly divide data based on business characteristics but also improves performance and increases data throughput. For more information, refer to Distributed Database Overview.

Next, use the CREATE statement to create a partitioned table named tb in the database db, with “TradeTime” and “SecurityID” as the partitioning columns. Enter and execute the following script into the editor.

CREATE TABLE "dfs://db"."tb"(
    TradeTime TIMESTAMP
    SecurityID SYMBOL
    TotalVolumeTrade LONG
    TotalValueTrade DOUBLE
)
partitioned by TradeTime, SecurityID

Click the 🗘 icon in the upper right corner of the database browser to view the created database and table.

You can execute the following script to view the schema of the partitioned table tb.

loadTable("dfs://db", "tb").schema().colDefs

Output:

Download the sample data testdata.csv. Execute the following script to import the sample data from disk into the distributed table. Note that "path_to_testdata.csv" should be replaced with the actual path to the sample data.

tmp = loadTable("dfs://db", "tb").schema().colDefs
schemaTB = table(tmp.name as name, tmp.typeString as type)
loadTextEx(dbHandle=database("dfs://db"), tableName="tb", partitionColumns=`TradeTime`SecurityID, filename="path_to_testdata.csv", schema=schemaTB)

After import, use loadTable to load the data into memory, then check with a SELECT query.

tb = loadTable("dfs://db", "tb")
select * from tb

Output:

Note: This example displays the result with two decimal places. You can click the ⚙ icon to customize the decimal places.

DolphinDB seamlessly combines SQL with functions and expressions. The following are some simple queries performed on the partitioned table tb.

Example 1.Count the number of rows.

Query the total number of records in tb.

SELECT count(*) FROM tb
//Output: 16

Example 2. Filter records with specific values.

Query all records satisfying SecurityID = `A00001 in tb.

SELECT * FROM tb WHERE SecurityID = `A00001

Output:

Example 3.Group records with group by.

Use group by to group the data by SecurityID and query the average of TotalVolumeTrade and the maximum of TotalValueTrade for each security.

SELECT avg(TotalVolumeTrade), max(TotalValueTrade) FROM tb group by SecurityID

Output:

Example 4. Group records with context by.

context by is a unique feature in DolphinDB for group calculation. Unlike group by, it returns a vector of the same size as the group's records, which can be used with aggregate functions, moving window functions, cumulative functions, etc., facilitating time series analysis.Use context by to group the data by SecurityID and query the last two records for each security.

SELECT TradeTime, SecurityID, deltas(TotalVolumeTrade) FROM tb CONTEXT BY SecurityID

Output:

Example 5.Rearrange columns on two dimensions.

pivot by is a unique feature in DolphinDB used to rearrange a column (or multiple columns) of a table on two dimensions. Use pivot by to specify TradeTime as the row index and SecurityID as the column index to view the values of TotalValueTrade.

select TotalValueTrade from tb pivot by TradeTime, SecurityID

Output:

To continue exploring DolphinDB, here are some things you might like to try:

• For Data Analysts: Focus on key SQL operations such as aggregation, time series data processing, and table joining. Pay special attention to streaming subscriptions and engines to tackle high-throughput, low-latency, and complex real-time analysis scenarios.
• For Database Developers: Focus on core concepts of databases and DolphinDB scripting language to leverage DolphinDB's high performance to the largest extent and develop more efficient applications.
• For Database Administrators: Focus on database maintenance and troubleshooting to ensure the stable performance and effective management of the DolphinDB system.

For any issues with DolphinDB, you can seek technical support through the following channels:

DolphinDB offers high-quality videos on YouTube, covering technical discussions, product introductions, and recorded public lectures. Feel free to explore these video resources!