Archive

Posts Tagged ‘SQL’

[Python]PostgreSQL字典/JSON类型递归自展开

July 15th, 2019 No comments

PostgreSql 习惯上会将特殊数据类型的各个节点按字典/JSON类型存储
程序中需要获得完整的数据信息的时候,需要对这个节点进行自展开。

以下使用global id方式进行展开,一般适用于SQL+NoSQL结合的系统使用

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
import sys, os
import numpy as np
 
def get_object_by_gid(id):
	for dict in data["json"]:
		if dict["gid"] == id:
	return dict.copy()
 
def self_exact_node(key):
	dict = get_object_by_gid(key)
	for k,v in dict.items():
		if k == "sub_item" :
			item_arr = []
			for id in v["gids"]:
				item_arr.append(self_exact_node(id))
			v["item_arr"] = item_arr.copy()
	return dict
 
def demo():
	data_exact = data.copy()
	for d in data_exact["json"]:
		d = self_exact_node(d["gid"])
 
def main():
	demo()
 
if __name__ == '__main__’:
	sys.exit(main())

其他玩法

1
2
3
4
5
6
7
8
# Global ID方式
"gid": "大分类2:中分类2:子分类2",
# 复合ID
"id":{"l1_cat":"大分类1","l2_cat":"中分类2","l3_cat":"子分类2"}
# 链表式
"chain":{"next_gid":"","pre_gid":"","head_gid":"","tail_gid":""}
# 二叉树式
"btree":{"next_sibling":"","child":”"}

Read more…

Categories: 语言编程 Tags: , , ,

PostgreSQL使用PL/SQL和游标实现按日期批量执行

March 26th, 2019 No comments

现有的DWH系统的是按天创建数据表的,使得定期维护变得麻烦,例如每个月底需要将按当月产生的临时表archive。

方式1.批量生成SQL,按固定的日期值生成一堆SQLSQL生成方法多样。但是需要确认全部sql是否正确。

方式2.编写PL/SQL function,使用游标方式批量执行。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
drop function fun_datecursor(from_date text,to_date text,cond text);
create or replace function fun_datecursor(from_date text,to_date text,cond text) returns text
as $ 
declare
    cur_date refcursor;
    buffer text := '';
    var_day date;
begin
open cur_date for execute 'SELECT generate_series('''|| from_date ||'''::date,'''|| to_date ||'''::date,'''|| cond ||''')'; 
    loop  --开始循环
        fetch cur_date into var_day;  --将游标指定的值赋值给变量
        if found then 
             ---------------------------------------
             --任意的逻辑,或调用其他function
             ---------------------------------------
            buffer:= buffer || to_char(var_day,'yyyyMMdd'); 
           else 
            exit; 
            end if; 
    end loop;  --结束循环
close cur_date;  --关闭游标
return buffer;
end
$ language plpgsql;

执行function

1
select fun_datecursor('2008-03-01','2008-03-31','1 day’);
Categories: 零敲碎打 Tags: , ,

PostgreSQL创建ReadOnly只读用户

March 26th, 2019 No comments

PostgreSQL可以通过schema和table级别对数据表进行只读控制
一般会使用PostgreSQL创建只读用户,然后给予相应的只读权限方式实现

通过使用

1
2
3
4
5
6
7
8
9
10
11
12
13
-- 创建readonly_user用户,密码为readonly_password
create user readonly_user with encrypted password 'readonly_password';
-- 设置readonly_user用户为只读事务
alter user readonly_user set default_transaction_read_only=on;
-- 授予usage权限给到readonly_user用户
grant usage on schema "public" to readonly_user;
-- 将默认"public"schema下新建表的读取权限授予给readonly_user
alter default privileges in schema "public" grant select on tables to readonly_user;
-- 授予select权限给到readonly_user用户
grant select on all tables in schema "public" to readonly_user;
grant select on all sequences in schema "public" to readonly_user;
-- 允许readonly_user用户连接到指定数据库
grant connect on database splrp_dev to readonly_user;

注意:
已有的数据表进行readonly设置,可以通过

1
2
3
4
5
-- 授予usage权限给到readonly_user用户
grant usage on schema "public" to readonly_user;
-- 授予select权限给到readonly_user用户
grant select on all tables in schema "public" to readonly_user;
grant select on all sequences in schema "public" to readonly_user;

对于将来创建的新表,则需要通过

1
2
-- 将默认"public"schema下新建表的读取权限授予给readonly_user
alter default privileges in schema "public" grant select on tables to readonly_user;

可以讲以后创建的table也赋予readonly_user只读权限。

Categories: 零敲碎打 Tags: ,

PostgreSQL查询表和index占用空间大小

November 12th, 2018 No comments

PostgreSQL查询表和index占用空间大小

PostgreSQL表和index占用空间大小信息存储在
information_schema.tables中
通过SQL可以查询到相应的统计数据

1
2
--查出单个表的大小
select pg_size_pretty(pg_relation_size('TABLENAME'));

查出表大小按大小含Index

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- 查出表大小按大小含Index
SELECT
"table_name",
pg_size_pretty(table_size) AS table_size,
pg_size_pretty(indexes_size) AS indexes_size,
pg_size_pretty(total_size) AS total_size
FROM (
SELECT
table_name,
SUBSTRING("table_name",1,10) as short_name,
pg_table_size(table_name) AS table_size,
pg_indexes_size(table_name) AS indexes_size,
pg_total_relation_size(table_name) AS total_size
FROM (
SELECT ('"' || table_schema || '"."' || table_name || '"') AS table_name
FROM information_schema.tables
) AS all_tables
where all_tables.table_name like '%TABLENAME%'
ORDER BY total_size DESC
) AS pretty_sizes

Read more…

PostgreSql查询正在执行的SQL和查询执行耗时的SQL

November 9th, 2018 No comments

运行在AWS RDS上的PostgreSql今天TransactionID耗尽,原因是有一个SQL执行占用CPU超过12小时

–查询正在执行的SQL

1
select * from pg_stat_activity where datname='schema名称';

–结束正在进行的R查询

1
select pg_cancel_backend(pid);

–结束正在执行CUD操作

1
select  pg_terminate_backend(pid)

Read more…

Categories: 系统管理, 语言编程 Tags: , ,

[Redis] 解决Redis运行时Cannot save in background问题

August 28th, 2015 No comments

Redis是一个开源的使用ANSI C语言编写、支持网络、可基于内存亦可持久化的日志型、Key-Value数据库,并提供多种语言的API。从2010年3月15日起,Redis的开发工作由VMware主持。从2013年5月开始,Redis的开发由Pivotal赞助。

Redis运行时经常出现 Cannot save in background. 问题,通常因为两个原因
1.Redis的占用的内存过大,导致BGSAVE时系统无法分配足够的内存给BGSAVE进程而导致错误。
碰到这种情况时,你可以这样操作,限制Redis的最大内存大小到系统内存的一半以内。
设置redis的内存可以在redis.conf 中修改 maxmemory 属性

1
   maxmemory  # 系统实际安装内存的一半以内,实验环境的话建议不要超过1/3

Read more…

修复SQLServer2005/2008/2012数据库质疑/Suspect状态

March 6th, 2015 No comments

昨天夜间机房断电后SQLServer2008服务器重启,早上Sharepoint系统不能登录,使用SQLServer的管理工具检查后发现,SharePoint_Config数据库上多了一个黄色的三角符号,并提示数据库为质疑/Suspect状态.
立即使用以下SQL脚本进行强制恢复数据库

1
2
3
4
5
6
7
8
9
    use master
    declare @databasename varchar(255)
    set @databasename='SharePoint_Config'
    sp_configure 'allow updates',1
    reconfigure with override
    --强制修改数据库状态
    update master..sysdatabases set status = 16 where name = @databasename
    --尝试恢复数据库
    dbcc dbrecover(@databasename, IGNOREERRORS)

结果还是质疑/Suspect状态,估计是数据库日志文件有损坏. 好在只是SharePoint_Config数据库,对用户数据没有影响。直接使用了以下方法进行了恢复数据库。
Read more…

Categories: 系统管理 Tags: ,