yujihamaguchi’s gists

yujihamaguchi / gist:1c6f57eafb33e45393e2

Created August 8, 2014 06:53

[Excel] Excellで可変リストを入力規則に設定したい場合

=OFFSET(INDIRECT("Sheet2!A2"),0,0,COUNTA(INDIRECT("Sheet2!$A$2:$A$30")),1)

yujihamaguchi / gist:feb4f790a10f8ca734df

Last active August 29, 2015 14:05

[Redshift] テーブル毎のディスクスペース確認

	SET SEARCH_PATH TO <schema name>;
	SELECT
	stv_tbl_perm.name AS table
	,COUNT(*) AS mb
	FROM
	stv_blocklist,
	stv_tbl_perm
	WHERE
	stv_blocklist.tbl = stv_tbl_perm.id
	AND stv_blocklist.slice = stv_tbl_perm.slice

yujihamaguchi / gist:b817e518117c63c73d8a

Created August 13, 2014 02:27

[Redshift] 列ごとの容量確認

	-- refer: http://dev.classmethod.jp/cloud/aws/amazon-redshift-tutorial-tuning-table-design/
	-- システム生成の列を除外する為にWHERE句にcol < 6がある
	select col, max(blocknum)
	from stv_blocklist b, stv_tbl_perm p
	where (b.tbl=p.id) and name ='<table name>'
	and col < 6
	group by name, col
	order by col;

yujihamaguchi / gist:82d93dd6cd23f6a35ab1

Last active August 29, 2015 14:06

[Redshift] テーブルの最終DML時間取得

	SELECT
	MAX(endtime) + INTERVAL '9 hour' AS last_query_time
	FROM
	stl_query r1
	WHERE
	EXISTS (SELECT *
	FROM
	stl_querytext r2
	WHERE
	r1.userid = r2.userid

yujihamaguchi / gist:ec5d4c6c31283e5214e7

Created November 4, 2014 09:31

[Redshift] テーブルの最終Analyze時刻その他を表示

	SELECT
	TO_CHAR(starttime + INTERVAL '9 hour', 'YYYY-MM-DD HH24:MI:SS') AS starttime
	,TO_CHAR(endtime + INTERVAL '9 hour', 'YYYY-MM-DD HH24:MI:SS') AS endtime
	,DATE_DIFF('sec', starttime, endtime) AS secs
	,text
	FROM
	svl_statementtext
	WHERE
	sequence = 0
	AND xid IN (

yujihamaguchi / [Redshift] 既存テーブルのDDLを生成する

Last active August 29, 2015 14:11

yujihamaguchi / gist:43f0fca95c15b33e4922

Created December 17, 2014 01:40

[Redshift] ロックを検出、プロセスの停止

	select current_time, c.relname, l.database, l.transaction, l.pid, a.usename, l.mode, l.granted from pg_catalog.pg_locks l join pg_catalog.pg_class c on c.oid = l.relation join pg_catalog.pg_stat_activity a on a.procpid = l.pid;

	set query_group to 'superuser';
	select pg_terminate_backend(<pid>);
	reset query_group;

yujihamaguchi / gist:d39f02da309540a2d397

Created December 17, 2014 05:18

[bash] 無限ループ1ライナー

while :; do {処理}; sleep 1; done

yujihamaguchi / gist:4e107ec6998b8f30e5b9

Created January 9, 2015 01:30

[Redshift] ユーザが削除できない時に試す処置

	REVOKE ALL ON DATABASE {db name} FROM {user id} CASCADE;
	REVOKE ALL ON SCHEMA {schema name 1} FROM {user id} CASCADE;
	REVOKE ALL ON SCHEMA {schema name 2} FROM {user id} CASCADE;
	...

yujihamaguchi / ex_perf.sql

Created February 6, 2015 03:37

[Tableau Server] レポートの表示時間を取得する

	---------------------------------------------------------------------------------------------
	-- 以下を実行し、レポート表示に要した時間を取得する。
	-- (Tableau Server データベースへの接続に関しては下記を参照)
	-- http://onlinehelp.tableau.com/current/server/ja-jp/help.htm#adminview_postgres.htm%3FTocPath%3D%25E7%25AE%25A1%25E7%2590%2586%25E8%2580%2585%25E3%2582%25AC%25E3%2582%25A4%25E3%2583%2589%7C%25E3%2582%25B5%25E3%2583%25BC%25E3%2583%2590%25E3%2583%25BC%25E3%2581%25AE%25E6%2593%258D%25E4%25BD%259C%7C%25E3%2582%25AB%25E3%2582%25B9%25E3%2582%25BF%25E3%2583%25A0%25E7%25AE%25A1%25E7%2590%2586%25E3%2583%2593%25E3%2583%25A5%25E3%2583%25BC%25E3%2581%25AE%25E4%25BD%259C%25E6%2588%2590%7C_____0
	---------------------------------------------------------------------------------------------

	SELECT
	r2.name AS site_name
	,r1.currentsheet AS report_name
	,r1.vizql_session AS session_id

Yuji Hamaguchi yujihamaguchi