Constantin Lungudatawise.dev·Jul 6, 2024A practical exercise working with ARRAYS and correlated subqueries in BigQueryHere's an interesting SQL problem, similar to one I had to solve the other day. It involves some of our favourite BigQuery ARRAYS, but also correlated subqueries. Say we have a table events that represents some events, together with the city and th...DiscussPractical BigQuerybigquery
Constantin Lungudatawise.dev·Jul 6, 2024Query parameters in BigQueryQuery parameters in BigQuery. You need to know about them if you're constructing queries based on user input. What do they do? They can help secure your query against SQL injection and can be recognized by the @ (for named usage) and ? (for positiona...Discuss·30 readsPractical BigQuerybigquery
Constantin Lungudatawise.dev·Jul 5, 2024System variables in BigQueryToday's quick post is about system variables in BigQuery. What are they? They are a special type of variables, available in scripts (multi-statement queries). You can use them to read (and sometimes write) query metadata during query execution. Here'...DiscussPractical BigQuerybigquery
Constantin Lungudatawise.dev·Jul 4, 2024Ingestion-time partitioning in BigQueryHave you ever used ingestion-time partitioning in BigQuery? It's a separate type of partitioning that distributes rows into partitions based on the time they land in BQ. Once such a table is defined, you can query the pseudocolumns PARTITIONDATE ...DiscussPractical BigQuerybigquery
Constantin Lungudatawise.dev·Jul 3, 2024Short, almost non-technical guide to SQL query tuning as a Data EngineerSo I spend quite a bit of time optimizing SQL data processing pipelines in BigQuery. Scale-wise, I might have an occasional encounter with a 10TB table. I have limited knowledge on what it does 'under the hood', but I find that there's a couple of si...DiscussPractical BigQuerySQL
Constantin Lungudatawise.dev·Jul 3, 2024DECLARE and SET variables in BigQueryHere's a part of procedural language that I use quite a lot in BigQuery. Whether you're looking to run a query based on a multiple values of a particular parameter, finding out the watermark for incremental loading or just doing some testing, you can...Discuss·45 readsPractical BigQuerybigquery
Constantin Lungudatawise.dev·Jun 26, 2024Comments in SQLLet's look at the available options in terms of SQL comments in BigQuery. If you ever need to pass on a note to the future you or a fellow developers about the reasoning behind a particular approach or something to watch out for in a query, you can w...Discuss·1 likePractical BigQuerySQL
Ahmad W Khanblog.ahmadwkhan.com·Jun 26, 2024Automating Ad Campaigns with Open AI and Python-DjangoOutline: Introduction Purpose of the blog post Importance of integrating OpenAI API into Ad Tech products Brief overview of what will be covered Understanding the Integration of OpenAI API in Ad Tech Overview of OpenAI API Benefits of using ...Discussopenai
Constantin Lungudatawise.dev·Jun 23, 2024NON-EQUI joins in SQLSo here's another post about SQL joins. Based on the type of condition we use for joining we distinguish equi joins and non-equi joins. Simply put:- equi joins: we're using the equality operator:tab_a.column_x = tab_b.column_y- non-equi joins: other ...DiscussPractical BigQuerySQL
Constantin Lungudatawise.dev·Jun 22, 2024LAX JSON conversion functions in BigQuerySo if you're looking to decompress after a long week and relax, check out the LAX conversion functions for handling JSON conversions in BigQuery. There are 4 separate functions: LAX_STRING, LAX_BOOL, LAX_FLOAT64, LAX_INT64 - with each one of them att...Discuss·1 likePractical BigQuerySQL