trino-iceberg 常用语句

## 查看建表语句

```sql
show create table table_name
```

## 查看表属性

```sql
SELECT * FROM "test_table$properties"
```

## 查看表历史变化

```sql
SELECT * FROM "test_table$history"
```

## 查看表的快照

```sql
SELECT * FROM "test_table$snapshots"
```

## 查看表的清单概述

```sql
SELECT * FROM "test_table$manifests"
```

## 查看表的分区

```sql
SELECT * FROM "test_table$partitions"
```

## 查看表的数据文件

```sql
SELECT * FROM "test_table$files"
```

## 查看表的参考分支标签等

```sql
SELECT * FROM "test_table$refs"
```

## 查看表的文件路径等

```sql
SELECT *, "$path", "$file_modified_time"
FROM example.web.page_views;

SELECT *
FROM example.web.page_views
WHERE "$path" = '/usr/iceberg/table/web.page_views/data/file_01.parquet'

SELECT *
FROM example.web.page_views
WHERE "$file_modified_time" = CAST('2022-07-01 01:02:03.456 UTC' AS timestamp with time zone)
```

## 查看指定快照的表数据

```sql
SELECT *
FROM example.testdb.customer_orders FOR VERSION AS OF 8954597067493422955

SELECT *
FROM example.testdb.customer_orders FOR TIMESTAMP AS OF TIMESTAMP '2022-03-23 09:59:29.803 Europe/Vienna'
```

## 回滚到指定快照

```sql
CALL example.system.rollback_to_snapshot('testdb', 'customer_orders', 8954597067493422955)
```

## 移除过期7天的快照

```sql
ALTER TABLE test_table EXECUTE expire_snapshots(retention_threshold => '7d')
```

## 移除过期7天的文件

```sql
ALTER TABLE test_table EXECUTE remove_orphan_files(retention_threshold => '7d')
```

## like 转义字符

```sql
select 'dwd_xxx' like 'dwd\_%' escape '\';
```