Drop Databases and Tables

In DolphinDB, you can delete a database or table with dropDatabase.

The following methods are provided to delete the data in a table:

  • Function dropTable deletes a table and its schema.

  • Function truncate removes rows of a table but keeps its schema.

  • Function dropPartition deletes specified partitions of a table.

  • delete statement deletes data with where clause.

Comparison of execution times using the above functions:

// create a database
dbName="dfs://testdb"
if(existsDatabase(dbName)){
        dropDatabase(dbName)
}
n=10000
ticker = rand(`MSFT`GOOG`FB`ORCL`IBM`PPT`AZH`ILM`ANZ,n);
id = rand(`A`B`C, n)
x=rand(1.0, n)
t=table(ticker, id, x)
db=database(directory=dbName, partitionType=HASH, partitionScheme=[STRING, 5])

// create tables
pt0 = db.createPartitionedTable(t, `pt, `ticker)
pt0.append!(t)
pt1 = db.createPartitionedTable(t, `pt1, `ticker)
pt1.append!(t)
pt2 = db.createPartitionedTable(t, `pt2, `ticker)
pt2.append!(t)
pt3 = db.createPartitionedTable(t, `pt3, `ticker)
pt3.append!(t)

// use delete statement
timer delete from pt0
Time elapsed: 795.314 ms

// use function dropPartition
timer for(i in 0:5){ if(existsPartition(dbName + "/Key"+ string(i), `pt1))dropPartition(dbHandle=db, partitionPaths="/Key"+ string(i), tableName=`pt1)}
Time elapsed: 11.724 ms

// use function truncate
timer truncate(dbName, "pt2")
Time elapsed: 5.278 ms

// use function dropTable
timer dropTable(db, "pt3")
Time elapsed: 5.582 ms