vertica-CUSTOM DESIGNER、 PROJECTION、procedure、空间对象

109 阅读 0 评论 72 点赞

我是靠谱客的博主饱满期待，这篇文章主要介绍vertica-CUSTOM DESIGNER、 PROJECTION、procedure、空间对象，现在分享给大家，希望可以做个参考。

一 .创建具有指定名称的设计
参考：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

复制代码

1
=> SELECT DESIGNER_CREATE_DESIGN('VMART_DESIGN');

--Set design properties

复制代码

1
=>DESIGNER_SET_DESIGN_TYPE ( 'VMART_DESIGN', 'INCREMENTAL' )

-- add TABLES

复制代码

1
 => SELECT DESIGNER_ADD_DESIGN_TABLES('VMART_DESIGN','online_sales.*');

-- add QUERIES

复制代码

1
2
 => 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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
  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://www.vertica.com/docs/9.1.x/HTML/index.htm#Authoring/AdministratorsGuide/Projections/WorkingWithProjections.htm%3FTocPath%3DAdministrator's%2520Guide%7CWorking%2520with%25C2%25A0Projections%7C_____0

https://blog.csdn.net/hmxz2nn/article/details/90312731

1.创建PROJECTION

复制代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
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;

刷新

复制代码

1
2
全量刷新:viid=>SELECT START_REFRESH();
指定刷新:viid=> select refresh('public.book');

查看刷新进度：

复制代码

1
SELECT *FROM PROJECTION_REFRESHES WHERE projection_name='book_projection1'

-- 查看某表所有的projection信息

复制代码

1
viid=> SELECT GET_PROJECTIONS('public.book');

删除:

复制代码

1
DROP PROJECTION public.book_projection1;

复制 table and projection

复制代码

1
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:

复制代码

1
2
3
4
5
6
7
8
9
10
11
12
13
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:

复制代码

1
2
#!/bin/bash
echo "hello planet argument: $1" >> /tmp/myprocedure.log

2. 安装

复制代码

1
2
3
4
$ /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:

复制代码

1
=> CREATE PROCEDURE helloplanet(arg1 varchar) AS 'helloplanet.sh' LANGUAGE 'external' USER 'dbadmin';

4. grant

复制代码

1
=> GRANT EXECUTE ON PROCEDURE tokenize(varchar) TO Bob, Jules, Operator;

5. revoke

复制代码

1
=> REVOKE EXECUTE ON PROCEDURE tokenize(varchar) FROM Bob;

6.drop

复制代码

1
=> DROP PROCEDURE helloplanet(arg1 varchar);

四 . 地理空间对象

参考：https://www.vertica.com/docs/9.1.x/HTML/index.htm#Authoring/AnalyzingData/Geospatial/GeospatialAnalytics/GeospatialAnalytics.htm%3FTocPath%3DAnalyzing%2520Data%7CGeospatial%2520Analytics%7C_____0

思路: 先创建测试表，再插入数据确定列具体长度，最后创建用于生产环境的表

复制代码

1
2
3
CREATE TABLE [[db-name.]schema.]table-name (
   column-name GEOMETRY[(length)],
   column-name GEOGRAPHY[(length)]);

STV_MemSize
以INTEGER形式返回空间对象的长度（以字节为单位）。

使用此功能可以确定空间数据的最佳列宽。
例子:

复制代码

1
=> CREATE TABLE mem_size_table (id int, geom geometry(800));

CREATE TABLE

复制代码

1
=> 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)))
>>.

复制代码

1
=> SELECT max(STV_MemSize(geom)) FROM mem_size_table;

max
-----
336
(1 row)

CREATE TABLE

复制代码

1
=> CREATE TABLE production_table(id int, geom geometry(336));

复制代码

1
=> INSERT INTO production_table SELECT * FROM mem_size_table;

OUTPUT
--------
3
(1 row)

DROP TABLE

复制代码

1
=> DROP mem_size_table;

最后

以上就是饱满期待最近收集整理的关于vertica-CUSTOM DESIGNER、 PROJECTION、procedure、空间对象的全部内容，更多相关vertica-CUSTOM内容请搜索靠谱客的其他文章。

本图文内容来源于网友提供，作为学习参考使用，或来自网络收集整理，版权属于原作者所有。

本文分类：vertica
浏览次数：109 次浏览
发布日期：2023-10-20 04:16:18
本文链接：https://www.kaopuke.com/article/k-p-k_13_u_23_o_26_f5_14_zk0.html

vertica-CUSTOM DESIGNER、 PROJECTION、procedure、空间对象

最后

评论列表共有 0 条评论

发表评论取消回复

vertica-CUSTOM DESIGNER、 PROJECTION、procedure、空间对象

最后

相关文章

评论列表共有 0 条评论

发表评论 取消回复

微信扫一扫：分享

发表评论取消回复