vertica常用命令1

1.建表

create table store.lw_test(cus_id varchar(30),cus_order varchar(30),price int,all_price number );

2.插入数据
inset into store.lw_test valuse('lw','001',20,20);

3.
insert into store.lw_test
select t.store_name,t.store_region,t.number_of_employees,t.annual_shrinkage from store.store_dimension t;

4.查询
select t.cus_id,sum(t.price) from store.lw_test t group by t.cus_id;

5.创建分区表：
CREATE TABLE store.store_sales_fact_part
( product_key integer not null,
store_key integer not null,
customer_key integer not null,
pos_transaction_number integer not null,
sales_dollar_amount integer,
transaction_time time not null
) PARTITION BY date_part('hour',transaction_time);

5. List of tables

VMartDB=> \dt

List of tables
Schema | Name | Kind | Owner
--------------+-----------------------+-------+---------
online_sales | call_center_dimension | table | dbadmin
online_sales | online_page_dimension | table | dbadmin
online_sales | online_sales_fact | table | dbadmin
public | customer_dimension | table | dbadmin
public | date_dimension | table | dbadmin
public | employee_dimension | table | dbadmin
public | inventory_fact | table | dbadmin
public | product_dimension | table | dbadmin
public | promotion_dimension | table | dbadmin
public | shipping_dimension | table | dbadmin
public | vendor_dimension | table | dbadmin
public | warehouse_dimension | table | dbadmin
store | store_dimension | table | dbadmin
store | store_orders_fact | table | dbadmin
store | store_sales_fact | table | dbadmin
(15 rows)

6. 查看表结构

VMartDB=> SELECT * FROM column_storage WHERE anchor_table_name = 'date_dimension';
node_name | column_name | row_count | used_bytes | encodings | compressions | wos_row_count | ros_row_count | ros_used_bytes | ros_count | projection_name | projection_schema | anchor_table_name | anchor_table_schema | anchor_table_column_name
--------------------+-------------------+-------------------------------+-----------+------------+--------------+------------------+---------------+---------------+----------------+-----------+-------------------+------------------------------------------------+-------------------+-------------------+-------------------+---------------------+--------------------
v_vmartdb_node0001 | date_key | 7314 | 2741 | Uncompressed | int common delta | 0 | 7314 | 2741 | 5 | date_dimension_DBD_4_rep_VMart_Design_node0001 | public | da

te_dimension | public | date_key

(1 rows)

7.删除字段
alter table APP.APP_PRODUCT_COMPARE_ANALYSIS_M2 drop column BASE_INTERNET_SVC_UNUM;

8.修改表所属用户
alter table XXXX owner to mid;

9.给表授权
grant select on stg.T_xxxx to app;

10 重命名表
alter table APP.APP_PRODUCT_COMPARE_ANALYSIS_M2 rename to APP.APP_PRODUCT_COMPARE_ANALYSIS_M;