We need to get data into DuckDB so that it can be queried, transformed, and analyzed. The files and example data for these exercises are available in the chapter_02 folder at https://github.com/PacktPublishing/Getting-Started-with-DuckDB/tree/main/chapter_02.

Important note

In this chapter, we will be learning how to load data into (and export data from) DuckDB using local files. DuckDB can also read and write data located in Simple Storage Service (S3)-compatible object stores, including AWS S3, Google Cloud Storage, and Azure Blob storage. We will explore using DuckDB to interact with object stores in Chapter 5.

CSV files are ubiquitous in the world of analytical data, which is why DuckDB comes with a powerful and flexible built-in CSV parser. The appeal of this simple text-based format is that CSV files are easy to inspect, and their tabular format is readily comprehended. While they are straightforward to produce and share, there are, however, often challenges when working with CSV files. Notably, they come in a wide variety of dialects and, often, non-standard variations. For example, despite their name, they sometimes use characters other than commas for delimiting each field—such as tabs, they may or may not have a header row with column names, and there are different approaches to escaping special characters, such as delimiters and quotes. When parsing a CSV file, the specific format of a CSV file can often be inferred but may need to be specified manually. Furthermore, CSV files don’t contain an embedded schema, meaning that conversion from text values...

JSON is a popular and open file format for storing and exchanging data and has good integration with many programming languages, libraries, and data systems. A JSON object is written inside curly braces and can contain multiple name-value pairs. A name-value pair consists of an attribute name in double quotes, followed by a colon, followed by a value. JSON values can include strings, numbers, and Boolean data types, as well as nested objects and array data types, which are represented as comma-separated sequences of values, wrapped with square braces. Here’s an example of a simple JSON object:

{"food_name":"Hawaiian Pizza", "quantity":6}

DuckDB can read JSON files with the read_json function in a variety of formats. In the wild, you may encounter a range of different types of JSON files, with records represented using different conventions. Two common flavors of JSON data that you may encounter in the wild are newline-delimited...

Apache Parquet is an open source file format that is designed for efficient storage and retrieval of data. Their columnar-oriented format combined with the use of compression to reduce storage space and I/O cost of reading and writing make these files well suited for storing and retrieving large amounts of structured and semi-structured data for analytical applications.

Parquet files are encoded in a binary format, so you cannot view them as text files as you might with a CSV file. Parquet files are self-describing in that each file contains both data and metadata describing the schema of the data within the file. This means that column names, their data types, and summary information about the number of rows and columns are encoded within the file. This contrasts with CSV and JSON files, which contain purely text data without an embedded schema. In addition to performance gains, this is one of the notable benefits of Parquet files, as their built-in schema...

Public datasets are collections of data that are made available to the public by various sources, such as governments, organizations, and researchers. Public datasets can be useful for analyzing trends and patterns in different domains, such as health, education, environment, or social impact.

DuckDB is a powerful tool for exploring, understanding, and gaining insights from public datasets. In this section, we’ll work with a public dataset that has been made available in CSV format. We’ll use DuckDB to load it in an appropriate form, summarize it, and export it to another format. This worked example will allow us to showcase some of DuckDB’s versatile features that make it well suited for analytical workflows.

Bike-share station readings

We are going to be exploring the Melbourne Bike Share dataset, which provides historical data from the Melbourne Bike Share service, which operated from 2010 to 2019, and is made available by...

So far, we have been importing data from a variety of data sources into DuckDB. We also often need to export data from DuckDB into a file, perhaps to transfer to another database system or to share our data with others. Let’s discuss how we can achieve that in the following sections.

Exporting a table into a CSV file

We can use the COPY ... TO command to export data from DuckDB to an external CSV, JSON, or Parquet file. This command can be called either with a table name or a query and will export the corresponding data to disk in the desired file format. Let’s try this out by creating a subset of our bike-share dataset and exporting it as a CSV file.

We’ll first create a table called bike_readings_april containing bike rides that occurred in April:

CREATE OR REPLACE TABLE bike_readings_april AS
SELECT *
FROM bikes
WHERE RUNDATE BETWEEN '2017-04-01' AND '2017-04-30';

With the bike_readings_april table created, let...

In this chapter, we learned how to load data into and unload data from DuckDB. We saw data as text formats in the form of CSV and JSON, as well as the self-describing binary columnar format, Apache Parquet.

We learned techniques to format data during import and skip records with errors, and we saw how DuckDB can support a variety of changing schemas and data types. We also learned how to find, process, and summarize public datasets and saw how DuckDB can be used to export data for consumption by analytical systems.

Now that we know how to load data into DuckDB, the next chapter will cover techniques for using DuckDB for data manipulation in order to transform your data. You will learn how to clean and reshape data using SQL and use these approaches to manipulate data from different sources and formats. You will also see how to interact with data located on remote systems, such as data located on remote web servers.