DEV Community: Hui Zheng (I/Trust/You)

Reducing GCP DataStream Sync Latency from PostgreSQL to BigQuery

Hui Zheng (I/Trust/You) — Sat, 29 Jun 2024 06:34:29 +0000

Jun 29 '24 Comments: 1 Answers: 0

I'm using GCP DataStream to sync data from PostgreSQL to BigQuery. However, I've noticed a delay of just over ten minutes in the data sync process. After digging into the documentation, I found that the issue is likely due to the default max_staleness configuration for the BigQuery destination tables. By…

Open Full Question

BigQuery SQL Tip: QUALIFY clause

Hui Zheng (I/Trust/You) — Fri, 10 Dec 2021 18:58:08 +0000

In any SQL statement, There are two types of columns.

columns available via the FROM clause
columns as SELECT list aliases

I learn something new about QUALIFY clause in BigQuery lately on how it could access these columns differently based on the position of window definition.

Please see example below.

WITH a AS (
    SELECT 
        *
    FROM UNNEST([STRUCT(1 AS val, 'in' AS canopy),
    (2, 'out'),
    (3, 'above'),
    (1, 'in')
    ])
)

-- Approach 1: QUALIFY only have access to columns available via the FROM clause

-- Case 1.1: 

-- SELECT
--     CASE WHEN canopy = 'above' THEN 'in'
--     ELSE canopy
--     END AS mapped_canopy,
--     val,
--     ROW_NUMBER() OVER w1 as rn,
-- FROM a
-- WHERE val IS NOT NULL
-- QUALIFY rn = 1
-- WINDOW w1 AS (PARTITION BY canopy)

-- Case 2: Invalid SQL

-- SELECT
--     CASE WHEN canopy = 'above' THEN 'in'
--     ELSE canopy
--     END AS mapped_canopy,
--     val,
--     ROW_NUMBER() OVER w1 as rn,
-- FROM a
-- WHERE val IS NOT NULL
-- QUALIFY rn = 1
-- WINDOW w1 AS (PARTITION BY mapped_canopy)

-- Approach 2: QUALIFY have access to both columns via the FROM clause and columns as SELECT list aliases

-- Case 3:

SELECT
    CASE WHEN canopy = 'above' THEN 'in'
    ELSE canopy
    END AS mapped_canopy,
    val
FROM a
WHERE val IS NOT NULL
QUALIFY ROW_NUMBER() OVER (PARTITION BY canopy) = 1

-- Case 4

-- SELECT
--     CASE WHEN canopy = 'above' THEN 'in'
--     ELSE canopy
--     END AS mapped_canopy,
--     val
-- FROM a
-- WHERE val IS NOT NULL
-- QUALIFY ROW_NUMBER() OVER (PARTITION BY mapped_canopy) = 1

Finally, a Brightness Control on MacOS that rules all monitors (external or not)

Hui Zheng (I/Trust/You) — Fri, 10 Dec 2021 17:50:19 +0000

Enough said,

I use multiple external monitors on my MacBook. The MacOS brightness control only works for built-in display, and it has always been a pain to adjust the brightness of those external monitors at night, until this comes.

https://github.com/MonitorControl/MonitorControl#readme

Finally, a Brightness Control on MacOS that rules all monitors (external or not)

Hui Zheng (I/Trust/You) — Wed, 03 Nov 2021 04:45:06 +0000

I use multiple external monitors on my MacBook. It has always been a pain to adjust the brightness of those external monitors at night. The MacOS brightness control only works for built-in display. The buttons on monitor device are the only way to adjust the external monitor.

Until this app comes along to rule it all.

https://github.com/MonitorControl/MonitorControl#readme

make nice code snippet for presentation

Hui Zheng (I/Trust/You) — Thu, 21 Oct 2021 19:00:14 +0000

I found a very good tool to make good looking code snippet in presentation.

It would show the code with syntax highlight in programming language that you chose.

It's very good. give it a try next time when you make a presentation.

https://romannurik.github.io/SlidesCodeHighlighter/