一 .创建具有指定名称的设计
参考:https://www.vertica.com/docs/9.1.x/HTML/index.htm#Authoring/AdministratorsGuide/DBDAPI/CategoriesOfDatabaseDesignerFunctions.htm
https://www.vertica.com/docs/9.1.x/HTML/index.htm#Authoring/SQLReferenceManual/Functions/VerticaFunctions/DatabaseDesigner/DESIGNER_CREATE_DESIGN.htm
-- create DESIGNER
=> SELECT DESIGNER_CREATE_DESIGN('VMART_DESIGN');
--Set design properties
=>DESIGNER_SET_DESIGN_TYPE ( 'VMART_DESIGN', 'INCREMENTAL' )
-- add TABLES
=> SELECT DESIGNER_ADD_DESIGN_TABLES('VMART_DESIGN','online_sales.*');
-- add QUERIES
=> SELECT DESIGNER_ADD_DESIGN_QUERIES('VMART_DESIGN', '/tmp/examples/vmart_queries.sql','true');
输出:
DESIGNER_ADD_DESIGN_QUERIES
-----------------------------
Number of accepted queries =9
Number of queries referencing non-design tables =0
Number of unsupported queries =0
Number of illegal queries =0
=> \x
Expanded display is on.
=> SELECT * FROM V_MONITOR.DESIGN.QUERIES
-[ RECORD 1 ]------------+-------------------
design_id | 45035996273705090
design_name | vmart_design
design_query_id | 1
design_query_id_index | 0
query_text | SELECT fat_content
FROM (
SELECT DISTINCT fat_content
FROM product_dimension
WHERE department_description
IN ('Dairy') ) AS food
ORDER BY fat_content
LIMIT 5;
weight | 1
design_query_search_path | v_dbd_vmart_design_vmart_design_ltt, "$user", public, v_catalog, v_monitor, v_internal
design_query_signature | 45035996273724651
-[ RECORD 2]-------------+-------------------
design_query_id | 2
design_query_id_index | 0
query_text | SELECT order_number, date_ordered
FROM store.store_orders_fact orders
WHERE orders.store_key IN (
SELECT store_key
FROM store.store_dimension
WHERE store_state = 'MA')
AND orders.vendor_key NOT IN (
SELECT vendor_key
FROM public.vendor_dimension
WHERE vendor_state = 'MA')
AND date_ordered < '2012-03-01';
weight | 1
design_query_search_path | v_dbd_vmart_design_vmart_design_ltt, "$user", public, v_catalog, v_monitor, v_internal
design_query_signature | 45035996273724508
二 . 自定义projection
https://blog.csdn.net/hmxz2nn/article/details/90312731
1.创建PROJECTION
CREATE PROJECTION IF NOT EXISTS public.book_projection1 (
book_id ENCODING RLE ACCESSRANK 1500,
GROUPED(book_name ENCODING RLE, book_price)
book_type,
recordtime)
AS SELECT
book_id,
book_name,
book_price,
book_type,
recordtime
FROM public.book
ORDER BY book_id
SEGMENTED BY HASH(book_id) UNSEGMENTED ALL NODES KSAFE 1;
刷新
全量刷新:viid=>SELECT START_REFRESH();
指定刷新:viid=> select refresh('public.book');
查看刷新进度:
SELECT *FROM PROJECTION_REFRESHES WHERE projection_name='book_projection1'
-- 查看某表所有的projection信息
viid=> SELECT GET_PROJECTIONS('public.book');
删除:
DROP PROJECTION public.book_projection1;
复制 table and projection
CREATE TABLE private.book LIKE public.book INCLUDING PROJECTIONS;
ACCESSRANK 参数解析:
用于优先考虑列访问速度
如果您测量并设置群集中存储位置的性能,Vertica将使用此信息来确定列的存储位置。有关更多信息,请参阅设置存储性能。
列如何排名
Vertica将投影排序顺序中包含的列存储在最快的可用存储位置上。投影排序顺序中未包括的列存储在较慢的磁盘上。每个投影的列排名如下:
排序顺序中的列具有最高优先级(数字> 1000)。
排序顺序的最后一列被赋予排名编号1001。
排序顺序中的倒数第二列被赋予排名号1002,依此类推,直到排序顺序中的第一列被赋予1000 +#排序列。
其余列的编号从1000-1开始,从1000开始,每列递减1。
然后,Vertica会将磁盘上从最高到最低的列存储在磁盘上。它将排名最高的列放在最快的磁盘上,而排名最低的列放在最慢的磁盘上。
覆盖默认列排名
您可以通过手动覆盖这些列的默认等级来修改存储在快速磁盘上的列。为此,请ACCESSRANK在列列表中设置关键字。确保使用尚未用于另一列的整数。例如,如果要为列赋予最快的访问等级,请使用明显高于1000 +排序列数的数字。这样,您可以随时间输入更多列,而不会影响您设置的访问级别。
The following example sets column store_key's access rank to 1500:
CREATE PROJECTION retail_sales_fact_p (
store_key ENCODING RLE ACCESSRANK 1500,
pos_transaction_number ENCODING RLE,
sales_dollar_amount,
cost_dollar_amount )
AS SELECT
store_key,
pos_transaction_number,
sales_dollar_amount,
cost_dollar_amount
FROM store.store_sales_fact
ORDER BY store_key
SEGMENTED BY HASH(pos_transaction_number) ALL NODES;
--------------------------------------------------------------------------------------------------------------------------------------
三 . 自定义存储过程
1.Create the file:
#!/bin/bash
echo "hello planet argument: $1" >> /tmp/myprocedure.log
2. 安装
$ /opt/vertica/bin/adminTools
$ admintools -t install_procedure -d vmartdb -f /scratch/helloworld.sh -p ownerpassword
Installing external procedure...
External procedure installed
3.Create the procedure with the following SQL:
=> CREATE PROCEDURE helloplanet(arg1 varchar) AS 'helloplanet.sh' LANGUAGE 'external' USER 'dbadmin';
4. grant
=> GRANT EXECUTE ON PROCEDURE tokenize(varchar) TO Bob, Jules, Operator;
5. revoke
=> REVOKE EXECUTE ON PROCEDURE tokenize(varchar) FROM Bob;
6.drop
=> DROP PROCEDURE helloplanet(arg1 varchar);
四 . 地理空间对象
思路: 先创建测试表,再插入数据确定列具体长度,最后创建用于生产环境的表
CREATE TABLE [[db-name.]schema.]table-name (
column-name GEOMETRY[(length)],
column-name GEOGRAPHY[(length)]);
STV_MemSize
以INTEGER形式返回空间对象的长度(以字节为单位)。
使用此功能可以确定空间数据的最佳列宽。
例子:
=> CREATE TABLE mem_size_table (id int, geom geometry(800));
CREATE TABLE
=> COPY mem_size_table (id, gx filler LONG VARCHAR, geom as ST_GeomFromText(gx)) FROM STDIN DELIMITER '|';
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>>1|POINT(3 5)
>>2|MULTILINESTRING((1 5, 2 4, 5 3, 6 6),(3 5, 3 7))
>>3|MULTIPOLYGON(((2 6, 2 9, 6 9, 7 7, 4 6, 2 6)),((0 0, 0 5, 1 0, 0 0)),((0 2, 2 5, 4 5, 0 2)))
>>\.
=> SELECT max(STV_MemSize(geom)) FROM mem_size_table;
max
-----
336
(1 row)
CREATE TABLE
=> CREATE TABLE production_table(id int, geom geometry(336));
=> INSERT INTO production_table SELECT * FROM mem_size_table;
OUTPUT
--------
3
(1 row)
DROP TABLE
=> DROP mem_size_table;