Recent content in Francesco Tisiot Ideas! on Data and Open Source
Tuning a SQL query is a mix of data driven discovery and magic art, until now… Aiven SQL query optimizer allows you to speed up your query by simply pasting the SQL and additional metadata information. Check it out at https://go.aiven.io/ft-sql-query-optimizer 
 
 

Since the beginning of my journey into the data world I’ve been keen on making professionals better at their data job. In the previous years that took the shape of creating materials in several different forms that could help people understand, use, and avoid mistakes on their data tool of choice. But now there’s much more into it: a trusted AI solution to help data professional in…
Testing a database performance is a must in every company. Despite everyone’s needs beings slightly different, a good starting point for PostgreSQL® database is using pgbench : a tool shipped with the PostgreSQL installation that allows you to stress test a local or remote database.
This blog post showcases how to install (on a Mac) and use pgbench to create load on a remote PostgreSQL…
Some time ago I found an interesting database file suffix I never faced before: the .dbf and saw around that it was first introduced in 1983 with dBASE II. This article showcases how we can automatically generate the PostgreSQL table and fill it with data using Python and dbfread .
You can query a JSON document in MySQL to find content within it with: 
 
 The JSON_CONTAINS function that will return if a JSON document is contained within another JSON document. The function returns 1 if the document is contained, 0 elsewhere. 
 The JSON_CONTAINS function that will return if a JSON document path is contained within another JSON document. The function returns 1 if…
Everyone is now talking about AI, and modern databases like PostgreSQL® are increasingly being adopted in companies’ AI journey as sources of data or key pieces of the AI infrastructure. Moreover there’s a new set projects that are solving PostgreSQL problems with AI.
A boolean value should only contain two values, True or False , but is it correct? Usually people assume so, but sometimes miss the fact that there could be the absence of the value all-together. In databases this is absence is usually stored as NULL and this blog showcases how to find them, use them properly and 11 lessons to learn to be a NULL Pro! 
 
 Keep in mind, it’s not only…
One of the most common tasks with strings is concatenation! This blog post showcases several techniques to perform it with MySQL.
You have a JSON dataset that you want to upload to a PostgreSQL table containing properly formatted rows and columns… How do you do it? 
 All the main sources like my own blog and others tell you to load the JSON in a dedicated temporary table containing a unique JSON column, then parse it and load into the target table. However there could be another way, avoiding the temp table!
How to load StackOverflow StackExchange data in a PostgreSQL® database!
Earlier this month I wrote a piece on solving Gunnar Morling interesting 1 billion rows challenge in PostgreSQL and ClickHouse . Since Aiven provides also MySQL, I wanted to give it a try. TLDR; The results are much slower than PG and ClickHouse, do you have any suggestion on how to improve?
Need to extract a specific substring out of a text in PostgreSQL®? Read here how!
Why PostgreSQL matters in the AI space as source, central component and target.
Last week the good old Gunnar Morling launched an interesting challenge about ordering 1 billion rows in Java. Like my ex colleague and friend Robin Moffat , I’m not at all a Java expert, and while Robin used DuckDB to solve the challenge, I did the same with PostgreSQL and ClickHouse.
Generative AI is revolutionizing how we work and interact. Yet for generative AI applications relying on private, regulated, or commercially valuable data, feeding the model with the right information is crucial. Failure could result in adverse consequences such as poor performance, confidential information exposure, or regulatory requirement breaches. An integrated data platform is a powerful way…
When attacking any system, taking control of its central nervous system is the first step in infiltrating the core infrastructure and gaining control over its vital functions. Translating this to organization data, a clear target is Apache Kafka, the data hub increasingly adopted for mission-critical systems. This session explores the topic of attacking and defending Apache Kafka, shedding light…
When sinking unix timestamps from Apache Kafka to OpenSearch/ElasticSearch using the dedicated connector, they are not recognized by default as timestamp in the target tech.
You have an Apache Kafka topic that you want to consume via REST APIs (think curl or just a web browser), how to do it? 
 The reply is Karapace an Open Source (Apache 2.0) tool providing Schema Registry and REST proxy functionality. 
 Let’s check out how to quickly run it on Docker. 
 Step 1: the Apache Kafka® Cluster 
 You need an Apache Kafka cluster up & running, if you…

 Useful Links 
 
 Aiven Signup 
 Code examples to run with Aiven for Apache Flink 
 Flink JDBC SQL Connector 
 Flink SQL CDC Connecto for PostgreSQL 
 
 💻
You can tabulate a JSON document (retrieve it as a row) in MySQL with the JSON_TABLE function.
You can remove a field from JSON document in MySQL with the JSON_REMOVE function.
You can insert a field in a JSON document in MySQL with the JSON_INSERT function.
You can edit a JSON document in MySQL with: 
 
 The JSON_SET function that will replace values for JSON paths that exists and add values for the ones that don’t exist. 
 The JSON_REPLACE function that will replace values for JSON paths that exists and ignore the ones that don’t exist. 

You can merge two JSON documents in MySQL with: 
 
 the JSON_MERGE_PRESERVE function to concatenate the document values 
 the JSON_MERGE_PATCH function to keep the latest value for each key 

You can create a JSON document from fields in Mysql® with the JSON_OBJECT function
To get the type of a JSON item in MySQL you need to use the JSON_TYPE function.
To extract an item from an array in MySQL you need to use the -> operator and the [item_number] JSON Path Syntax .
MySQL® offers three ways to extract fields from a JSON object: 
 
 the -> operator to extract the field as JSON 
 the ->> operator to extract the field as text 
 the JSON_EXTRACT function 
 
 Both operators use the JSON Path Syntax
To load JSON data into a MySQL column you need to include it as string.
This series covers how to solve common problems on JSON datasets with MySQL and it includes (links will appear once the target pages are up):
In the modern world, it’s rare to have the data in the same shape and platform from the beginning till the end of its journey. Yes, some technologies can achieve quite a good range of functionalities but sometimes at the expense of precision, developer experience or performance. Therefore, to achieve better or faster results, people might select a new tool for a precise task and start an…

 So… what does a Staff Developer Advocate do, and how is it different from the Senior Developer Advocate role you were doing before? 
 
 I got this question three times in the last two weeks after my promotion from Senior to Staff (🎉🎉🎉🎉), from various people in my network.
How and Why using Schema Registry with Apache Kafka
A talk about JSON handling in PostgreSQL: from data loading to query to indexing.
Strings are one of the most used types in databases; they can store pretty much any data and don’t enforce any rules on the inserted input. This talk showcases the risk of using strings and how to mitigate them to ensure data quality.
PostgreSQL® offers two types of data types to handle JSON data, JSON and JSONB . This doc showcases how to load JSON data into a column.
This page contains all the links related to the SOFT (Scalable Observable Fast Trustworthy) methodology for defining robust data platforms.
Technical scalability is one of the main drivers of a data platform, as mentioned in the SOFT methodology . But what are the options? In this blog we’ll evaluate the direction of scalability and the tradeoffs you might encounter in the decision process.
PostgreSQL® offers two types of data types to handle JSON data, JSON and JSONB , and provides two different ways to tabulate a JSON to a record:
PostgreSQL® offers two types of data types to handle JSON data, JSON and JSONB , and provides two different ways to tabulate a JSON to a recordset:
PostgreSQL® offers two types of data types to handle JSON data, JSON and JSONB , you can use the jsonb_set function to remove the null values for JSONB columns.
PostgreSQL® offers two types of data types to handle JSON data, JSON and JSONB , you can use the - operator to remove an item from an array.
PostgreSQL® offers two types of data types to handle JSON data, JSON and JSONB , and provides two different ways to remove fields from a JSON document:
PostgreSQL® offers two types of data types to handle JSON data, JSON and JSONB , you can use the json_strip_nulls ( jsonb_strip_nulls for JSONB) function to remove the null values.
PostgreSQL® offers two types of data types to handle JSON data, JSON and JSONB , you can use the opearator || to concatenate two JSON objects.
PostgreSQL® offers two types of data types to handle JSON data, JSON and JSONB , you can use the function json_object ( jsonb_object for JSONB ) to create a JSON object from an array of key/value pairs.
PostgreSQL® offers two types of data types to handle JSON data, JSON and JSONB , you can use the function json_object ( jsonb_object for JSONB ) to create a JSON object from two arrays of keys and values.
PostgreSQL® offers two types of data types to handle JSON data, JSON and JSONB , you can use the function array_to_json ( array_to_jsonb for JSONB ) to create a JSON array from an existing array.
PostgreSQL® offers two types of data types to handle JSON data, JSON and JSONB , you can use the function json_build_array ( jsonb_build_array for JSONB ) to create a JSON array from a list of elements.
PostgreSQL® offers two types of data types to handle JSON data, JSON and JSONB , you can use several functions to extract the keys and related values in tabular format.