This is an automated email from the ASF dual-hosted git repository.
morningman pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/incubator-doris.git
The following commit(s) were added to refs/heads/master by this push:
new 65ff464 [Feature] Support show data order by (#5770)
65ff464 is described below
commit 65ff464e3d178d8e02c55fd94f6c4c2010407d5c
Author: DinoZhang <[email protected]>
AuthorDate: Wed May 19 09:27:27 2021 +0800
[Feature] Support show data order by (#5770)
Currently, the `show data` does not support sorting. When the number of
tables increases, it is inconvenient to manage. Need to support sorting
like:
```
mysql> show data order by ReplicaCount desc,Size asc;
+-----------+-------------+--------------+
| TableName | Size | ReplicaCount |
+-----------+-------------+--------------+
| table_c | 3.102 KB | 40 |
| table_d | .000 | 20 |
| table_b | 324.000 B | 20 |
| table_a | 1.266 KB | 10 |
| Total | 4.684 KB | 90 |
| Quota | 1024.000 GB | 1073741824 |
| Left | 1024.000 GB | 1073741734 |
+-----------+-------------+--------------+
```
---
.../sql-statements/Data Manipulation/SHOW DATA.md | 28 +++-
.../sql-statements/Data Manipulation/SHOW DATA.md | 26 +++-
fe/fe-core/src/main/cup/sql_parser.cup | 8 +-
.../org/apache/doris/analysis/ShowDataStmt.java | 167 +++++++++++++++++----
.../apache/doris/analysis/ShowDataStmtTest.java | 19 ++-
5 files changed, 209 insertions(+), 39 deletions(-)
diff --git a/docs/en/sql-reference/sql-statements/Data Manipulation/SHOW
DATA.md b/docs/en/sql-reference/sql-statements/Data Manipulation/SHOW DATA.md
index 6240782..71d7e8c 100644
--- a/docs/en/sql-reference/sql-statements/Data Manipulation/SHOW DATA.md
+++ b/docs/en/sql-reference/sql-statements/Data Manipulation/SHOW DATA.md
@@ -33,7 +33,7 @@ This statement is used to show the amount of data, the number
of replica and num
Syntax:
```
-SHOW DATA [FROM db_name[.table_name]];
+SHOW DATA [FROM db_name[.table_name]] [ORDER BY ...];
```
Explain:
@@ -46,6 +46,10 @@ Explain:
4. The `Total` row in the result set represents the summary row. The `Quota`
row indicates the current quota of the database. The `Left` line indicates the
remaining quota.
+5. If you want to check the size of each Partition, please refer to `help show
partitions`.
+
+6. Arbitrary column combinations can be sorted using ORDER BY.
+
## example
1. Display the data volume, replica size, aggregate data volume and aggregate
replica count of each table of default DB.
@@ -70,7 +74,8 @@ Explain:
```
SHOW DATA FROM example_db.test;
-
+ ```
+ ```
+-----------+-----------+-----------+--------------+----------+
| TableName | IndexName | Size | ReplicaCount | RowCount |
+-----------+-----------+-----------+--------------+----------+
@@ -80,7 +85,26 @@ Explain:
| | Total | 80.000 | 90 | |
+-----------+-----------+-----------+--------------+----------+
```
+3. Can be combined and sorted according to the data volume, replica count,and
number of rows,etc.
+ ```
+ SHOW DATA ORDER BY ReplicaCount desc,Size asc;
+ ```
+
+ ```
+ +-----------+-------------+--------------+
+ | TableName | Size | ReplicaCount |
+ +-----------+-------------+--------------+
+ | table_c | 3.102 KB | 40 |
+ | table_d | .000 | 20 |
+ | table_b | 324.000 B | 20 |
+ | table_a | 1.266 KB | 10 |
+ | Total | 4.684 KB | 90 |
+ | Quota | 1024.000 GB | 1073741824 |
+ | Left | 1024.000 GB | 1073741734 |
+ +-----------+-------------+--------------+
+ ```
+
## keyword
SHOW,DATA
diff --git a/docs/zh-CN/sql-reference/sql-statements/Data Manipulation/SHOW
DATA.md b/docs/zh-CN/sql-reference/sql-statements/Data Manipulation/SHOW DATA.md
index 8361033..e371c0e 100644
--- a/docs/zh-CN/sql-reference/sql-statements/Data Manipulation/SHOW DATA.md
+++ b/docs/zh-CN/sql-reference/sql-statements/Data Manipulation/SHOW DATA.md
@@ -33,7 +33,7 @@ under the License.
语法：
```
-SHOW DATA [FROM db_name[.table_name]];
+SHOW DATA [FROM db_name[.table_name]] [ORDER BY ...];
```
说明：
@@ -48,6 +48,8 @@ SHOW DATA [FROM db_name[.table_name]];
5. 如果想查看各个 Partition 的大小，请参阅 `help show partitions`。
+6. 可以使用 ORDER BY 对任意列组合进行排序。
+
## example
1. 展示默认 db 的各个 table 的数据量，副本数量，汇总数据量和汇总副本数量。
@@ -72,7 +74,9 @@ SHOW DATA [FROM db_name[.table_name]];
```
SHOW DATA FROM example_db.test;
+ ```
+ ```
+-----------+-----------+-----------+--------------+----------+
| TableName | IndexName | Size | ReplicaCount | RowCount |
+-----------+-----------+-----------+--------------+----------+
@@ -82,7 +86,27 @@ SHOW DATA [FROM db_name[.table_name]];
| | Total | 80.000 | 90 | |
+-----------+-----------+-----------+--------------+----------+
```
+
+3. 可以按照数据量、副本数量、统计行数等进行组合排序
+ ```
+ SHOW DATA ORDER BY ReplicaCount desc,Size asc;
+ ```
+
+ ```
+ +-----------+-------------+--------------+
+ | TableName | Size | ReplicaCount |
+ +-----------+-------------+--------------+
+ | table_c | 3.102 KB | 40 |
+ | table_d | .000 | 20 |
+ | table_b | 324.000 B | 20 |
+ | table_a | 1.266 KB | 10 |
+ | Total | 4.684 KB | 90 |
+ | Quota | 1024.000 GB | 1073741824 |
+ | Left | 1024.000 GB | 1073741734 |
+ +-----------+-------------+--------------+
+ ```
+
## keyword
SHOW,DATA
diff --git a/fe/fe-core/src/main/cup/sql_parser.cup
b/fe/fe-core/src/main/cup/sql_parser.cup
index ff76913..062379a 100644
--- a/fe/fe-core/src/main/cup/sql_parser.cup
+++ b/fe/fe-core/src/main/cup/sql_parser.cup
@@ -2467,13 +2467,13 @@ show_param ::=
RESULT = new ShowAlterStmt(type, db, parser.where, orderByClause,
limitClause);
:}
/* Show data statement: used to show data size of specified range */
- | KW_DATA
+ | KW_DATA order_by_clause:orderByClause
{:
- RESULT = new ShowDataStmt(null, null);
+ RESULT = new ShowDataStmt(null, null, orderByClause);
:}
- | KW_DATA KW_FROM table_name:dbTblName
+ | KW_DATA KW_FROM table_name:dbTblName order_by_clause:orderByClause
{:
- RESULT = new ShowDataStmt(dbTblName.getDb(), dbTblName.getTbl());
+ RESULT = new ShowDataStmt(dbTblName.getDb(), dbTblName.getTbl(),
orderByClause);
:}
| opt_tmp:tmp KW_PARTITIONS KW_FROM table_name:tblName opt_wild_where
order_by_clause:orderByClause limit_clause: limitClause
{:
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/analysis/ShowDataStmt.java
b/fe/fe-core/src/main/java/org/apache/doris/analysis/ShowDataStmt.java
index e8eed90..aacf7f1 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/analysis/ShowDataStmt.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/analysis/ShowDataStmt.java
@@ -17,7 +17,6 @@
package org.apache.doris.analysis;
-import com.google.common.collect.Lists;
import org.apache.doris.catalog.Catalog;
import org.apache.doris.catalog.Column;
import org.apache.doris.catalog.Database;
@@ -34,12 +33,19 @@ import org.apache.doris.common.ErrorReport;
import org.apache.doris.common.Pair;
import org.apache.doris.common.UserException;
import org.apache.doris.common.util.DebugUtil;
+import org.apache.doris.common.util.OrderByPair;
import org.apache.doris.mysql.privilege.PrivPredicate;
import org.apache.doris.qe.ConnectContext;
import org.apache.doris.qe.ShowResultSetMetaData;
import com.google.common.base.Strings;
+import com.google.common.collect.ComparisonChain;
+import com.google.common.collect.ImmutableList;
+import com.google.common.collect.Lists;
+import com.google.common.collect.Maps;
+import com.google.common.collect.Ordering;
+import java.util.ArrayList;
import java.util.Arrays;
import java.util.Comparator;
import java.util.List;
@@ -64,16 +70,28 @@ public class ShowDataStmt extends ShowStmt {
.addColumn(new Column("ReplicaCount",
ScalarType.createVarchar(20)))
.addColumn(new Column("RowCount",
ScalarType.createVarchar(20)))
.build();
+ public static final ImmutableList<String> SHOW_TABLE_DATA_META_DATA_ORIGIN
= new ImmutableList.Builder<String>()
+ .add("TableName").add("Size").add("ReplicaCount")
+ .build();
+
+ public static final ImmutableList<String> SHOW_INDEX_DATA_META_DATA_ORIGIN
= new ImmutableList.Builder<String>()
+
.add("TableName").add("IndexName").add("Size").add("ReplicaCount").add("RowCount")
+ .build();
private String dbName;
private String tableName;
List<List<String>> totalRows;
+ List<List<Object>> totalRowsObject = Lists.newArrayList();
- public ShowDataStmt(String dbName, String tableName) {
+ private List<OrderByElement> orderByElements;
+ private List<OrderByPair> orderByPairs;
+
+ public ShowDataStmt(String dbName, String tableName, List<OrderByElement>
orderByElements) {
this.dbName = dbName;
this.tableName = tableName;
this.totalRows = Lists.newArrayList();
+ this.orderByElements = orderByElements;
}
@Override
@@ -93,6 +111,20 @@ public class ShowDataStmt extends ShowStmt {
ErrorReport.reportAnalysisException(ErrorCode.ERR_BAD_DB_ERROR,
dbName);
}
+ // order by
+ if (orderByElements != null && !orderByElements.isEmpty()) {
+ orderByPairs = new ArrayList<>();
+ for (OrderByElement orderByElement : orderByElements) {
+ if (!(orderByElement.getExpr() instanceof SlotRef)) {
+ throw new AnalysisException("Should order by column");
+ }
+ SlotRef slotRef = (SlotRef)orderByElement.getExpr();
+ int index = analyzeColumn(slotRef.getColumnName(),tableName);
+ OrderByPair orderByPair = new OrderByPair(index,
!orderByElement.getIsAsc());
+ orderByPairs.add(orderByPair);
+ }
+ }
+
if (tableName == null) {
db.readLock();
try {
@@ -132,17 +164,36 @@ public class ShowDataStmt extends ShowStmt {
} finally {
olapTable.readUnlock();
}
- Pair<Double, String> tableSizePair =
DebugUtil.getByteUint(tableSize);
- String readableSize =
DebugUtil.DECIMAL_FORMAT_SCALE_3.format(tableSizePair.first) + " "
- + tableSizePair.second;
-
- List<String> row = Arrays.asList(table.getName(),
readableSize, String.valueOf(replicaCount));
- totalRows.add(row);
+ //|TableName|Size|ReplicaCount|
+ List<Object> row = Arrays.asList(table.getName(),
tableSize, replicaCount);
+ totalRowsObject.add(row);
totalSize += tableSize;
totalReplicaCount += replicaCount;
} // end for tables
+ // sort by
+ if (orderByPairs != null && !orderByPairs.isEmpty()) {
+ // k-> index, v-> isDesc
+ Map<Integer, Boolean> sortMap = Maps.newLinkedHashMap();
+ for (OrderByPair orderByPair : orderByPairs) {
+ sortMap.put(orderByPair.getIndex(),
orderByPair.isDesc());
+
+ }
+ totalRowsObject.sort(sortRows(sortMap));
+ }
+
+ // for output
+ for (List<Object> row : totalRowsObject) {
+ //|TableName|Size|ReplicaCount|
+ Pair<Double, String> tableSizePair =
DebugUtil.getByteUint((long)row.get(1));
+ String readableSize =
DebugUtil.DECIMAL_FORMAT_SCALE_3.format(tableSizePair.first) + " "
+ + tableSizePair.second;
+ List<String> result =
Arrays.asList(String.valueOf(row.get(0)), readableSize,
+ String.valueOf(row.get(2)));
+ totalRows.add(result);
+ }
+
Pair<Double, String> totalSizePair =
DebugUtil.getByteUint(totalSize);
String readableSize =
DebugUtil.DECIMAL_FORMAT_SCALE_3.format(totalSizePair.first) + " "
+ totalSizePair.second;
@@ -205,30 +256,48 @@ public class ShowDataStmt extends ShowStmt {
indexRowCount += mIndex.getRowCount();
}
- Pair<Double, String> indexSizePair =
DebugUtil.getByteUint(indexSize);
- String readableSize =
DebugUtil.DECIMAL_FORMAT_SCALE_3.format(indexSizePair.first) + " "
- + indexSizePair.second;
-
- List<String> row = null;
- if (i == 0) {
- row = Arrays.asList(tableName,
- olapTable.getIndexNameById(indexId),
- readableSize,
String.valueOf(indexReplicaCount),
- String.valueOf(indexRowCount));
- } else {
- row = Arrays.asList("",
- olapTable.getIndexNameById(indexId),
- readableSize,
String.valueOf(indexReplicaCount),
- String.valueOf(indexRowCount));
- }
+ String indexName = olapTable.getIndexNameById(indexId);
+ //
.add("TableName").add("IndexName").add("Size").add("ReplicaCount").add("RowCount")
+ List<Object> row = Arrays.asList(tableName, indexName,
indexSize, indexReplicaCount, indexRowCount);
+ totalRowsObject.add(row);
totalSize += indexSize;
totalReplicaCount += indexReplicaCount;
- totalRows.add(row);
i++;
} // end for indices
+ // sort by
+ if (orderByPairs != null && !orderByPairs.isEmpty()) {
+ // k-> index, v-> isDesc
+ Map<Integer, Boolean> sortMap = Maps.newLinkedHashMap();
+ for (OrderByPair orderByPair : orderByPairs) {
+ sortMap.put(orderByPair.getIndex(),
orderByPair.isDesc());
+
+ }
+ totalRowsObject.sort(sortRows(sortMap));
+ }
+
+ // for output
+ for (int index = 0;index<= totalRowsObject.size() -1;index++) {
+ //| TableName| IndexName | Size | ReplicaCount | RowCount |
+ List<Object> row = totalRowsObject.get(index);
+ List<String> result;
+ Pair<Double, String> tableSizePair =
DebugUtil.getByteUint((long)row.get(2));
+ String readableSize =
DebugUtil.DECIMAL_FORMAT_SCALE_3.format(tableSizePair.first) + " "
+ + tableSizePair.second;
+ if (index == 0) {
+ result = Arrays.asList(tableName, String.valueOf(1),
+ readableSize, String.valueOf(3),
+ String.valueOf(row.get(4)));
+ } else {
+ result = Arrays.asList("", String.valueOf(1),
+ readableSize, String.valueOf(3),
+ String.valueOf(row.get(4)));
+ }
+ totalRows.add(result);
+ }
+
Pair<Double, String> totalSizePair =
DebugUtil.getByteUint(totalSize);
String readableSize =
DebugUtil.DECIMAL_FORMAT_SCALE_3.format(totalSizePair.first) + " "
+ totalSizePair.second;
@@ -240,6 +309,39 @@ public class ShowDataStmt extends ShowStmt {
}
}
+ public static int analyzeColumn(String columnName,String tableName) throws
AnalysisException {
+ ImmutableList<String> titles = SHOW_TABLE_DATA_META_DATA_ORIGIN;
+ if(tableName != null){
+ titles = SHOW_INDEX_DATA_META_DATA_ORIGIN;
+ }
+ for (String title : titles) {
+ if (title.equalsIgnoreCase(columnName)) {
+ return titles.indexOf(title);
+ }
+ }
+
+ throw new AnalysisException("Title name[" + columnName + "] does not
exist");
+ }
+
+ private static Comparator<List<Object>> sortRows(Map<Integer, Boolean>
sortMap) {
+ Ordering ordering = Ordering.natural();
+
+ return new Comparator<List<Object>>() {
+ @Override
+ public int compare(final List<Object> o1, final List<Object> o2) {
+ ComparisonChain comparisonChain = ComparisonChain.start();
+
+ for (Map.Entry<Integer, Boolean> sort : sortMap.entrySet()) {
+ int index = sort.getKey();
+ boolean isDesc = sort.getValue();
+ comparisonChain = comparisonChain.compare(o1.get(index),
o2.get(index),
+ isDesc ? ordering.reverse() : ordering);
+ }
+ return comparisonChain.result();
+ }
+ };
+ }
+
public boolean hasTable() {
return this.tableName != null;
}
@@ -266,10 +368,19 @@ public class ShowDataStmt extends ShowStmt {
}
builder.append(" FROM `").append(dbName).append("`");
- if (tableName == null) {
- return builder.toString();
+ if (tableName != null) {
+ builder.append(".`").append(tableName).append("`");
+ }
+
+ // Order By clause
+ if (orderByElements != null) {
+ builder.append(" ORDER BY ");
+ for (int i = 0; i < orderByElements.size(); ++i) {
+ builder.append(orderByElements.get(i).getExpr().toSql());
+ builder.append((orderByElements.get(i).getIsAsc()) ? " ASC" :
" DESC");
+ builder.append((i + 1 != orderByElements.size()) ? ", " : "");
+ }
}
- builder.append(".`").append(tableName).append("`");
return builder.toString();
}
diff --git
a/fe/fe-core/src/test/java/org/apache/doris/analysis/ShowDataStmtTest.java
b/fe/fe-core/src/test/java/org/apache/doris/analysis/ShowDataStmtTest.java
index f54426e..a9209e0 100644
--- a/fe/fe-core/src/test/java/org/apache/doris/analysis/ShowDataStmtTest.java
+++ b/fe/fe-core/src/test/java/org/apache/doris/analysis/ShowDataStmtTest.java
@@ -35,6 +35,8 @@ import org.junit.Test;
import mockit.Expectations;
import mockit.Mocked;
+import java.util.Arrays;
+
public class ShowDataStmtTest {
@Mocked
@@ -132,16 +134,25 @@ public class ShowDataStmtTest {
@Test
public void testNormal() throws AnalysisException, UserException {
- ShowDataStmt stmt = new ShowDataStmt(null, null);
+ ShowDataStmt stmt = new ShowDataStmt(null, null, null);
stmt.analyze(analyzer);
Assert.assertEquals("SHOW DATA FROM `testCluster:testDb`",
stmt.toString());
Assert.assertEquals(3, stmt.getMetaData().getColumnCount());
Assert.assertEquals(false, stmt.hasTable());
-
- stmt = new ShowDataStmt("testDb", "test_tbl");
+
+ SlotRef slotRefOne = new SlotRef(null, "ReplicaCount");
+ OrderByElement orderByElementOne = new OrderByElement(slotRefOne,
false, false);
+ SlotRef slotRefTwo = new SlotRef(null, "Size");
+ OrderByElement orderByElementTwo = new OrderByElement(slotRefTwo,
false, false);
+
+ stmt = new ShowDataStmt("testDb", "test_tbl",
Arrays.asList(orderByElementOne, orderByElementTwo));
stmt.analyze(analyzer);
- Assert.assertEquals("SHOW DATA FROM
`default_cluster:testDb`.`test_tbl`", stmt.toString());
+ Assert.assertEquals("SHOW DATA FROM
`default_cluster:testDb`.`test_tbl` ORDER BY `ReplicaCount` DESC, `Size` DESC",
stmt.toString());
Assert.assertEquals(5, stmt.getMetaData().getColumnCount());
Assert.assertEquals(true, stmt.hasTable());
+
+ stmt = new ShowDataStmt(null, null, Arrays.asList(orderByElementOne,
orderByElementTwo));
+ stmt.analyze(analyzer);
+ Assert.assertEquals("SHOW DATA FROM `testCluster:testDb` ORDER BY
`ReplicaCount` DESC, `Size` DESC", stmt.toString());
}
}
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
