10 Commonly Overlooked Details in Data Importing

When containHeader is not specified, DolphinDB defaults to assigning column names as col0, col1, …, etc.

For instance, when executing loadText("/xxx/colName.csv") to import the data, the result is as follows:

Note that since “col0” and “col1” contain discrete values, DolphinDB defaults to parsing them as SYMBOL type. In contrast, columns “col2” through “col5” contain integer data, which is parsed as INT type by default. As a result, the original column name “totalSale” cannot be correctly mapped in “col2”, leaving the first row empty. Furthermore, the column names in “col3” through “col5” only retain the numeric portion that can be parsed (e.g., 2023).

When containHeader = true is specified, DolphinDB prefixes a "c" to column names that start with a number, ensuring valid column names.

For example, when executing loadText("/xxx/colName.csv", containHeader = true) to import the data, the result is as follows:

By specifying containHeader = true, column names starting with a number are correctly handled by simply adding a "c" prefix, which is the recommended approach. Therefore, when importing a text file using loadText, if the header contains column names starting with a number, containHeader = true should always be specified.

Executing loadText("/xxx/type.csv") results in the following:

By executing extractTextSchema("/xxx/type.csv"), it can be observed that the “ticker” column is incorrectly inferred as INT type, which does not meet expectations:

You can use the extractTextSchema function to first view the inferred results, then specify the “ticker” column's type as SYMBOL. The modified result can then be passed as the schema parameter to the loadText function for importing, which will yield the expected outcome.

schema = extractTextSchema("/xxx/type.csv")
update schema set type =`SYMBOL where name = "ticker"
loadText("/xxx/type.csv", schema=schema)

Executing loadText("/xxx/notdate.csv") results in the following:

Explicitly specify the column type (e.g., SYMBOL) via the schema parameter during import to ensure correct parsing.

Example:

schema = table(`id`ticker`price as name, `INT`SYMBOL`DOUBLE as type)
loadText("/xxx/notdate.csv", schema = schema)

Result:

Loading the file into memory with loadText before writing it to the database takes 771.618 ms.

timer{
    t = loadText("/xxx/data.csv")
    loadTable("dfs://test_load","data").append!(t)
}

>> Time elapsed: 771.618ms

In contrast, using loadTextEx to import the file directly to the database takes only 384.097 ms.

timer{
    loadTextEx(database("dfs://test_load"), "data", "date", "/xxx/data.csv", sortColumns=`id)
}

>> Time elapsed: 384.097ms

loadTextEx offers increasing performance benefits as the dataset grows.

Using loadText to load the file into memory, applying nullFill! to handle null values, and then writing to the database takes 802.23 ms.

timer{
    t = loadText("/xxx/dataWithNULL.csv")
    t.nullFill!(0.0)
    loadTable("dfs://test_load","data").append!(t)
}

>> Time elapsed: 802.23ms

In contrast, using loadTextEx with the transform parameter to import and preprocess data in one step takes only 385.086 ms.

timer{
    loadTextEx(database("dfs://test_load"), "data", "date", "/xxx/dataWithNULL.csv", transform = nullFill!{, 0.0}, sortColumns=`id)
}

>> Time elapsed: 385.086 ms

If the “timestamp” column is specified as TIMESTAMP during import, the result will be all null values.

schema = extractTextSchema("/xxx/time.csv")
update schema set type = "TIMESTAMP" where name = "timestamp"
loadText("/xxx/time.csv", schema=schema)

Result:

The correct approach is to import the ”timestamp” column as LONG type, then use the replaceColumn! function and timestamp function to convert it to TIMESTAMP type, achieving the expected result.

t = loadText("/xxx/time.csv")
replaceColumn!(t, `timestamp, timestamp(exec timestamp from t))
t

Although converting long integers to the TIMESTAMP type allows them to be displayed in a readable time format, DolphinDB parses all timestamps as UTC by default. If the original timestamps were generated based on local time, you may need to adjust for time zone differences as described in the next section.

Directly using the timestamp function to convert a LONG value to TIMESTAMP results in UTC time, which is 8 hours behind Beijing Time.

t = loadText("/xxx/localtime.csv")
replaceColumn!(t, `timestamp, timestamp(exec timestamp from t))
t

Result:

Using the time zone conversion function localtime to convert the UTC time to local time (in this example, the local time is UTC+8).

t = loadText("/xxx/localtime.csv")
replaceColumn!(t, `timestamp, localtime(timestamp(exec timestamp from t)))
t

Result:

Alternatively, using the convertTZ function to convert between two specified time zones.

t = loadText("/xxx/localtime.csv")
replaceColumn!(t, `timestamp, convertTZ(timestamp(exec timestamp from t), "UTC", "Asia/Shanghai"))
t

Result:

In the following example, a DSN connection string is used to connect to PostgreSQL, resulting in the authentication failure mentioned above:

odbcConfig = "DSN=PIE_PGSQL;Server="+ip+";port="+port+";DATABASE="+db+";Uid="+userName+";Password="+password+";"
conn = odbc::connect(odbcConfig)

>> FATAL: password authentication failed for user "pie_dev"

DolphinDB’s ODBC plugin on Linux is built on unixODBC. To verify whether a connection string is correctly configured, you can use the isql tool provided by unixODBC, which operates independently of DolphinDB. If the same DSN string results in an error with isql, the issue is likely with the connection string itself rather than with the DolphinDB plugin.

[28P01][unixODBC]FATAL: password authentication failed for user "pie_dev"
[ISQL]ERROR: Could not SQLDriverConnect

Refer to the Connection String Reference for the standard ODBC connection string format for PostgreSQL:

Modify the connection string according to the standard format, and the connection will succeed:

odbcConfig = "Driver={PostgreSQL};Server="+ip+";port="+port+";DATABASE="+db+";Uid="+userName+";Password="+password+";"
conn = odbc::connect(odbcConfig)

If decompression and data import are performed simultaneously on the same disk where DolphinDB stores its data—such as decompressing a .zip file while using loadTextEx to import a CSV—can result in an import time of about 1 minute and 41 seconds.

timer loadTextEx(database("dfs://test_load"), "data", "SecurityID", "/xxx/mdl_6_28_0.csv", sortColumns=`SecurityID`UpdateTime)

>> Time elapsed: 101156.78ms

During the import process, the disk read/write speed is as follows:

• Disk read speed: 16.2 KB/s
• Disk write speed: 32.6 MB/s
• Disk read volume in the past minute: 1.1 GB
• Disk write volume in the past minute: 1.1 GB
• Remaining disk space: 1.3 TB
• Total disk capacity: 2.2 TB
• Available disk space: 58.74%

Performing both decompression and import on the same disk concurrently significantly slows down the data import speed.

When the disk is idle and no decompression tasks are running, directly using the loadTextEx function to import the same file takes about 46 seconds:

timer loadTextEx(database("dfs://test_load"), "data", "SecurityID", "/xxx/mdl_6_28_0.csv", sortColumns=`SecurityID`UpdateTime)

>> Time elapsed: 46203.117ms

The disk read/write speed during this process is as follows:

• Disk read speed: 174.8 MB/s
• Disk write speed: 138.7 MB/s
• Disk read volume in the past minute: 3.5 GB
• Disk write volume in the past minute: 3.5 GB
• Remaining disk space: 1.3 TB
• Total disk capacity: 2.2 TB
• Available disk space: 58.23%

To improve data import performance, avoid performing data extraction and import on the same disk simultaneously. Separating these tasks or using different disks for each can reduce disk I/O contention and enhance import speed.

For example, parallel import of CSV files into the same partition (2023.08M) is considered:

Submit the import tasks using submitJob:

filePath = "/xxx/dailyData"
for (i in files(filePath).filename){
    submitJob("loadCSVTest", "load daily files to monthly db", loadTextEx, database("dfs://test_month"), "data", "date", filePath+i)
}

Upon checking the task completion status, only one task succeeds, while others fail due to write-write conflicts, throwing error code S00002.

To avoid write-write conflicts, import the files sequentially:

def loadDaily(filePath){
	for (i in files(filePath).filename){
		loadTextEx(database("dfs://test_month"), "data",`date,filePath+i,sortColumns=`ID)
	}
}

path = "/xxx/dailyData/"
submitJob("loadCSVTest","load daily files to monthly db",loadDaily, path)

Upon checking the task completion status, the data is successfully imported without errors.