聚合国内IT技术精华文章,分享IT技术精华,帮助IT从业人士成长

Transfer Redshift SQL to BigQuery SQL

2020-08-20 15:01 浏览: 1477347 次 我要评论(0 条) 字号:

In my recent work, I need to run some SQL snippet from Redshift on Google’s BigQuery platform. Since different data warehouses have a different recipe for SQL, the transferring work couldn’t be avoided.

Here comes some tricks:

RedshiftBigQuery
field::VARCHARCAST(field AS String)
isnull(), nvl()ifnull()
dateadd()date_add()
datediff()date_diff()
unionunion all
field ILIKE patternUPPER(field) LIKE pattern
split_part(string, delimiter, part)split(string, delimiter)[safe_offset(part)]

In Redshift we can select columns like this:

SELECT
  SQRT(score) AS new_score,
  new_score * 10 
FROM ...

But in BigQuery we couldn’t use column name from “AS”. The SQL in BigQuery should be:

SELECT
  SQRT(score) AS new_score,
  SQRT(score) * 10 
FROM ...

And, BigQuery has the “WITH” clause to replace the “temporary table”, which is very powerful:

WITH result AS (
   WITH example AS ( SELECT * FROM `dataset.table` )
   SELECT * FROM example
)

SELECT * FROM result


网友评论已有0条评论, 我也要评论

发表评论

*

* (保密)

Ctrl+Enter 快捷回复