Instantly share code, notes, and snippets.

valerybriz/snowflake_query_costs.sql

Last activeSeptember 17, 2022 11:49

Show Gist options

Star(0)You must be signed in to star a gist
Fork(0)You must be signed in to fork a gist

Learn more about clone URLs
Clone this repository at <script src="https://gist.github.com/valerybriz/2dc14c19ab2699c0184594f6de4e4f50.js"></script>
Save valerybriz/2dc14c19ab2699c0184594f6de4e4f50 to your computer and use it in GitHub Desktop.

Clone this repository at <script src="https://gist.github.com/valerybriz/2dc14c19ab2699c0184594f6de4e4f50.js"></script>

Save valerybriz/2dc14c19ab2699c0184594f6de4e4f50 to your computer and use it in GitHub Desktop.

Download ZIP

Queries to monitor snowflake costs and queries

Raw

snowflake_query_costs.sql

	-- monitorear queries con los costos más altos
	WITH WAREHOUSE_SIZEAS
	(
	SELECT WAREHOUSE_SIZE, NODES
	FROM (
	SELECT'XSMALL'AS WAREHOUSE_SIZE,1AS NODES
	UNION ALL
	SELECT'SMALL'AS WAREHOUSE_SIZE,2AS NODES
	UNION ALL
	SELECT'MEDIUM'AS WAREHOUSE_SIZE,4AS NODES
	UNION ALL
	SELECT'LARGE'AS WAREHOUSE_SIZE,8AS NODES
	UNION ALL
	SELECT'XLARGE'AS WAREHOUSE_SIZE,16AS NODES
	UNION ALL
	SELECT'2XLARGE'AS WAREHOUSE_SIZE,32AS NODES
	UNION ALL
	SELECT'3XLARGE'AS WAREHOUSE_SIZE,64AS NODES
	UNION ALL
	SELECT'4XLARGE'AS WAREHOUSE_SIZE,128AS NODES
	)
	),
	QUERY_HISTORYAS
	(
	SELECTQH.QUERY_ID
	,QH.QUERY_TEXT
	,QH.USER_NAME
	,QH.ROLE_NAME
	,QH.EXECUTION_TIME
	,QH.WAREHOUSE_SIZE
	FROMSNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY QH
	WHERE START_TIME> DATEADD(month,-2,CURRENT_TIMESTAMP())
	)

	SELECTQH.QUERY_ID
	,'https://'\|\| current_account()\|\|'.snowflakecomputing.com/console#/monitoring/queries/detail?queryId='\|\|QH.QUERY_IDAS QU
	,QH.QUERY_TEXT
	,QH.USER_NAME
	,QH.ROLE_NAME
	,QH.EXECUTION_TIMEas EXECUTION_TIME_MILLISECONDS
	,(QH.EXECUTION_TIME/(1000))as EXECUTION_TIME_SECONDS
	,(QH.EXECUTION_TIME/(1000*60))AS EXECUTION_TIME_MINUTES
	,(QH.EXECUTION_TIME/(10006060))AS EXECUTION_TIME_HOURS
	,WS.WAREHOUSE_SIZE
	,WS.NODES
	,(QH.EXECUTION_TIME/(10006060))*WS.NODESas RELATIVE_PERFORMANCE_COST

	FROM QUERY_HISTORY QH
	JOIN WAREHOUSE_SIZE WSONWS.WAREHOUSE_SIZE=upper(QH.WAREHOUSE_SIZE)
	ORDER BY RELATIVE_PERFORMANCE_COSTDESC
	LIMIT200
	;


	-- Monitorear costos de una query por warehouse, tabla, etc

	SELECT
	:datebucket(start_time),
	SUM( to_decimal(
	(decode(warehouse_size,'X-Small',1,'Small',2,'Medium',4,'Large',8,'X-Large',16,'2X-Large',32,'3X-Large',64,'4X-Large',128,'5X-Large',256,'6X-Large',512,-1)/60/60)(QUERY_LOAD_PERCENT/100)(EXECUTION_TIME/1000),38,8)
	) EstimatedCredits,
	SUM( execution_time )
	FROMsnowflake.account_usage.query_history
	where start_time= :daterange
	--and warehouse_name = 'COMPUTE_WH'
	--and query_text like '%instructores%'
	group by :datebucket(start_time)
	order by :datebucket(start_time);

	-- Obtener el costo promedio de las queries por warehouse
	set credit_price=4;--edit this value to reflect your credit price

	SELECT
	COALESCE(WC.WAREHOUSE_NAME,QC.WAREHOUSE_NAME)AS WAREHOUSE_NAME
	,QC.QUERY_COUNT_LAST_MONTH
	,WC.CREDITS_USED_LAST_MONTH
	,WC.CREDIT_COST_LAST_MONTH
	,CAST((WC.CREDIT_COST_LAST_MONTH/QC.QUERY_COUNT_LAST_MONTH)ASdecimal(10,2) )AS COST_PER_QUERY

	FROM (
	SELECT
	WAREHOUSE_NAME
	,COUNT(QUERY_ID)as QUERY_COUNT_LAST_MONTH
	FROMSNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
	WHERE TO_DATE(START_TIME)>= TO_DATE(DATEADD(month,-1,CURRENT_TIMESTAMP()))
	GROUP BY WAREHOUSE_NAME
	) QC
	JOIN (

	SELECT
	WAREHOUSE_NAME
	,SUM(CREDITS_USED)as CREDITS_USED_LAST_MONTH
	,SUM(CREDITS_USED)*($CREDIT_PRICE)as CREDIT_COST_LAST_MONTH
	FROMSNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY
	WHERE TO_DATE(START_TIME)>= TO_DATE(DATEADD(month,-1,CURRENT_TIMESTAMP()))
	GROUP BY WAREHOUSE_NAME
	) WC
	ONWC.WAREHOUSE_NAME=QC.WAREHOUSE_NAME

	ORDER BY COST_PER_QUERYDESC
	;

	--

Movatterモバイル変換

valerybriz/snowflake_query_costs.sql