# dropPartition {#droppartition}

## Syntax {#syntax}

`dropPartition(dbHandle, partitionPaths, tableName, [forceDelete=false], [deleteSchema=false])`

## Arguments {#arguments}

**dbHandle** is a DolphinDB database handle.

**partitionPaths** can be specified in two ways:

-   By path: *partitionPaths* is a STRING scalar/vector indicating the path of one or multiple partitions. Each string must start with "/". For composite partitions, the path must include all partition levels.

-   By condition: *partitionPaths* is a scalar or vector indicating the value\(s\) in the partitioning column. The system will drop all partitions containing these values. For composite partitions, *partitionPaths* is a tuple where each element is a filtering condition for each partition level \(starting from the first level\). If you do not want to apply filtering at a certain partition level, leave the corresponding element empty.


**tableName** is a string indicating a table name. It can be left empty if the database chunk granularity is at DATABASE level \(i.e., [database](database.md): *chunkGranularity* = 'DATABASE'\). Otherwise, it is a required parameter.

**forceDelete** \(optional\) is a Boolean value. If set to true, the specified partition\(s\) will be deleted even if the partition\(s\) is recovering. The default value is false.

**Note:** When using the `dropPartition` function with *forceDelete*=false, the number of available replicas for the chunks involved in the transaction must be greater than or equal to the configured *dfsReplicationFactor*.

**deleteSchema** \(optional\) is a Boolean value. The default value is false, indicating that only the data in the selected partitions will be deleted, but the partition schema \(which you can check with `schema().partitionSchema`\) is kept. When the following conditions are satisfied, you can delete the schema of the selected partitions along with the partition data by setting *deleteSchema* to true:

-   There's only one table in the database.
-   The partitioning type of the database is VALUE.
-   For composite partitions, the first level of partitioning type must be VALUE, and only the first level of partitions are selected for deletion.

## Details {#details}

Delete data from one or multiple partitions from a DFS database.

Please note that *dropPartition* only deletes data from selected partitions. It does not change the partitioning scheme. We do not need to reestablish these partitions if we need to append new data to them.

If *tableName* is specified: delete one or multiple partitions of the given table.

If *tableName* is not specified: delete one or multiple partitions of all tables with this partition.

## Examples {#examples}

The script should be executed on a data node or compute node of a cluster.

```
n=1000000
ID=rand(150, n)
dates=2017.08.07..2017.08.11
date=rand(dates, n)
x=rand(10.0, n)
t=table(ID, date, x)
dbDate = database(, VALUE, 2017.08.07..2017.08.11)
dbID = database(, RANGE, 0 50 100 150)
db = database("dfs://compoDB", COMPO, [dbDate, dbID])
pt = db.createPartitionedTable(t, `pt, `date`ID)
pt.append!(t);
```

The script above created a database with composite partition. The first level is a value partition with partitioning column of date, and the second level is a range partition with partitioning column of ID.

Example 1. Delete one partition

Use either of the following ways to delete the partition "/20170807/0\_50".

\(1\) Specify the partition path.

```
dropPartition(dbHandle=database("dfs://compoDB"), partitionPaths="/20170807/0_50");
```

**Note:** The *dbHandle* of `dropPartition` can also be specified as `db`, the variable used during database creation.

\(2\) Specify the filtering condition.

```
dropPartition(dbHandle=database("dfs://compoDB"), partitionPaths=[2017.08.07, 0]);
```

Here 0 means the partition of \[0, 50\). We can choose any number from 0 to 49 to represent this partition.

Example 2. Delete a first level partition

Use either of the following ways to delete the first level partition of 2017.08.08.

\(1\) Specify the path of all partitions under 2017.08.08.

```
partitions=["/20170808/0_50","/20170808/50_100","/20170808/100_150"]
dropPartition(dbHandle=database("dfs://compoDB"), partitionPaths=partitions);
```

\(2\) Specify the filtering condition.

```
dropPartition(dbHandle=database("dfs://compoDB"), partitionPaths=2017.08.08, tableName=`pt);
```

After the deletion, check the table partitioning scheme with [schema](../s/schema.md)

```
schema(db);
/*output
partitionSchema->([2017.08.11,2017.08.10,2017.08.09,2017.08.08,2017.08.07],[0,50,100,150])
partitionSites->
partitionTypeName->[VALUE,RANGE]
atomic->TRANS
databaseDir->dfs://compoDB
chunkGranularity->TABLE
engineType->OLAP
partitionType->[1,2]
*/
```

We can see that "2017.08.08" is still in *partitionSchema* as `dropPartition` only deleted the data from this partition but kept its schema. Since "2017.08.08" is a first level VALUE partition, we can also delete its schema from the table partitioning scheme along with its data by specifying *deleteSchema* = true:

```
dropPartition(dbHandle=database("dfs://compoDB"), partitionPaths=2017.08.08, tableName=`pt, deleteSchema = true);
```

Check the table partitioning scheme with [schema](../s/schema.md) - "2017.08.08" is no longer in *partitionSchema*:

```
schema(db);
/* output
partitionSchema->([2017.08.11,2017.08.10,2017.08.09,2017.08.07],[0,50,100,150])
...
*/
```

Example 3. Delete a second level partition

Use either of the following ways to delete the second level partition of \[0,50\).

\(1\) Specify the path of all partitions of \[0,50\).

```
partitions=["/20170807/0_50","/20170808/0_50","/20170809/0_50","/20170810/0_50","/20170811/0_50"]
dropPartition(dbHandle=database("dfs://compoDB"), partitionPaths=partitions);
```

\(2\) Specify the filtering condition.

```
dropPartition(dbHandle=database("dfs://compoDB"), partitionPaths=[,[0]]);
```

Example 4. Delete multiple same level partitions

To delete the second level partitions of \[0,50\) and \[100,150\):

```
dropPartition(dbHandle=database("dfs://compoDB"), partitionPaths=[,[0,100]]);
```

Example 5: Modify data in a distributed table on disk

To revise data in a distributed table, we need to update the entire partitions that the rows to be updated belong to. The following example adds 10 to column x of the rows with date=2017.08.10 and ID=88 in the distributed table pt.

\(1\) First load the data of the partition with date=2017.08.10 and ID=88 into memory.

```
tmp=select * from loadTable("dfs://compoDB","pt") where date=2017.08.10 and 50<=ID<100 ;
```

\(2\) Then add 10 to column x of table tmp:

```
update tmp set x=x+10 where date=2017.08.10 and ID=88;
```

\(3\) Delete data in the relevant partition:

```
dropPartition(dbHandle=database("dfs://compoDB"), partitionPaths="/20170810/50_100", tableName=`pt);
```

\(4\) Append table tmp to table pt:

```
pt.append!(tmp);
```

