Column

Free Copy of Apache Iceberg the Definitive Guide
Free Apache Iceberg Crash Course
Iceberg Lakehouse Engineering Video Playlist In the first post of this series, we introduced the Apache Parquet file format and touched upon one of its key features—columnar storage. Now, we’ll take a deeper dive into what this columnar storage model is, how it works, and why it’s so efficient for big data analytics. Understanding Parquet's columnar architecture is key to leveraging its full potential in optimizing data storage and query performance. What is Columnar Storage? Columnar storage means that instead of storing rows of data together, the data for each column is stored separately. This might seem counterintuitive at first, but it has major benefits for certain types of workloads, particularly those where you’re analyzing or aggregating specific columns rather than accessing entire rows. In a row-based format like CSV or JSON, data is written and read one row at a time. Each row stores all fields together in sequence. On the other hand, in a columnar format like Parquet, all values for a single column are stored together. For instance, if you have a dataset with columns for Name, Age, and Salary, all the values for the Name column are stored in one block, all the values for the Age column are stored in another, and so on. Why is Columnar Storage Efficient? The efficiency of columnar storage becomes clear when we consider the type of operations typically performed on large datasets in analytics. Let’s break down the advantages. 1. Faster Query Performance Columnar storage shines when your queries focus on a subset of columns. For example, if you want to calculate the average salary of employees in a large dataset, Parquet allows you to scan just the Salary column without reading the entire dataset. In a row-based format, even though you're only interested in one column, the system has to read all the data in every row to retrieve the values for that column. This results in a lot of unnecessary I/O operations, slowing down query performance. With Parquet, only the columns you need are read, making queries significantly faster. 2. Better Compression Parquet's columnar structure also improves compression. Since similar data types are stored together, compression algorithms can be applied more effectively. For example, if a column contains repeated values or data that follows a consistent pattern (such as dates or integers), it can be compressed more efficiently. By grouping similar values together, columnar formats enable algorithms like dictionary encoding or run-length encoding to achieve high compression ratios. This leads to smaller file sizes, which means reduced storage costs and faster data transfers. 3. Efficient Aggregation Columnar storage is ideal for aggregation queries, such as calculating sums, averages, or counts. These types of operations often focus on specific columns. With Parquet, only the relevant columns need to be read into memory, which not only improves query speed but also reduces the overall resource usage. 4. Batch Processing and Parallelization Another benefit of Parquet’s columnar model is that it enables better parallel processing. Since columns are stored independently, data processing engines like Apache Spark can read different columns in parallel, further speeding up query execution. This makes Parquet a great fit for distributed computing environments, where parallelism is key to achieving high performance. How Parquet Organizes Data Understanding how Parquet organizes data internally can help you fine-tune how you store and query your datasets. Columns and Row Groups: Parquet organizes data into row groups, which contain chunks of column data. A row group contains all the data for a subset of rows, but the data for each column is stored separately. This allows for efficient I/O when reading subsets of rows or columns.
Pages: Within each column chunk, data is further divided into pages. Parquet uses pages to store column data more granularly, which helps optimize compression and read performance. Each page is typically a few megabytes in size, and Parquet stores statistics about the data in each page, making it easier to skip irrelevant pages during query execution. Use Cases for Columnar Storage Columnar storage formats like Parquet are most effective in the following scenarios: Analytics-Heavy Workloads: If your workload involves a lot of analytical queries (e.g., calculating averages, filtering by certain columns), columnar formats will provide significant performance gains.
Big Data Environments: Parquet is commonly used in distributed data environments where large datasets are stored in cloud data lakes (e.g., AWS S3, Google Cloud Storage). It works seamlessly with frameworks like Apache Spark and Presto, which are built to process data at scale.
Data Warehousing: When designing data warehouses, storing data in Parquet allows you to run complex analytical queries efficiently while reducing storage costs due to Parquet’s high compression. When Not to Use Columnar Storage While columnar storage offers significant advantages for read-heavy, analytical workloads, it may not be the best option for all use cases. For example, transactional systems that involve frequent, small updates to data (like an online store's transaction log) may perform better with row-based formats, which are optimized for write-heavy operations. In such cases, the overhead of reading and writing data in columnar format may outweigh its benefits. Conclusion Parquet’s columnar storage model is what makes it a powerful tool for big data analytics. By organizing data by columns, Parquet allows for faster query performance, better compression, and more efficient aggregation. It’s designed to excel in environments where read-heavy workloads dominate and when your queries often target specific columns rather than entire datasets. In the next blog post, we’ll dive deeper into the file structure of Parquet, exploring how data is organized into row groups, pages, and columns to optimize both storage and retrieval. Stay tuned for part 3: Parquet File Structure: Pages, Row Groups, and Columns. Free Copy of Apache Iceberg the Definitive Guide Free Apache Iceberg Crash Course Iceberg Lakehouse Engineering Video Playlist Free Copy of Apache Iceberg the Definitive Guide Free Copy of Apache Iceberg the Definitive Guide Free Apache Iceberg Crash Course Free Apache Iceberg Crash Course Iceberg Lakehouse Engineering Video Playlist Iceberg Lakehouse Engineering Video Playlist In the first post of this series, we introduced the Apache Parquet file format and touched upon one of its key features—columnar storage. Now, we’ll take a deeper dive into what this columnar storage model is, how it works, and why it’s so efficient for big data analytics. Understanding Parquet's columnar architecture is key to leveraging its full potential in optimizing data storage and query performance. What is Columnar Storage? Columnar storage means that instead of storing rows of data together, the data for each column is stored separately. This might seem counterintuitive at first, but it has major benefits for certain types of workloads, particularly those where you’re analyzing or aggregating specific columns rather than accessing entire rows. In a row-based format like CSV or JSON, data is written and read one row at a time. Each row stores all fields together in sequence. On the other hand, in a columnar format like Parquet, all values for a single column are stored together. For instance, if you have a dataset with columns for Name , Age , and Salary , all the values for the Name column are stored in one block, all the values for the Age column are stored in another, and so on. Name Age Salary Name Age Why is Columnar Storage Efficient? The efficiency of columnar storage becomes clear when we consider the type of operations typically performed on large datasets in analytics. Let’s break down the advantages. 1. Faster Query Performance Faster Query Performance Columnar storage shines when your queries focus on a subset of columns. For example, if you want to calculate the average salary of employees in a large dataset, Parquet allows you to scan just the Salary column without reading the entire dataset. Salary In a row-based format, even though you're only interested in one column, the system has to read all the data in every row to retrieve the values for that column. This results in a lot of unnecessary I/O operations, slowing down query performance. With Parquet, only the columns you need are read, making queries significantly faster. 2. Better Compression Better Compression Parquet's columnar structure also improves compression. Since similar data types are stored together, compression algorithms can be applied more effectively. For example, if a column contains repeated values or data that follows a consistent pattern (such as dates or integers), it can be compressed more efficiently. By grouping similar values together, columnar formats enable algorithms like dictionary encoding or run-length encoding to achieve high compression ratios. This leads to smaller file sizes, which means reduced storage costs and faster data transfers. dictionary encoding run-length encoding 3. Efficient Aggregation Efficient Aggregation Columnar storage is ideal for aggregation queries, such as calculating sums, averages, or counts. These types of operations often focus on specific columns. With Parquet, only the relevant columns need to be read into memory, which not only improves query speed but also reduces the overall resource usage. 4. Batch Processing and Parallelization Batch Processing and Parallelization Another benefit of Parquet’s columnar model is that it enables better parallel processing. Since columns are stored independently, data processing engines like Apache Spark can read different columns in parallel, further speeding up query execution. This makes Parquet a great fit for distributed computing environments, where parallelism is key to achieving high performance. How Parquet Organizes Data Understanding how Parquet organizes data internally can help you fine-tune how you store and query your datasets. Columns and Row Groups: Parquet organizes data into row groups, which contain chunks of column data. A row group contains all the data for a subset of rows, but the data for each column is stored separately. This allows for efficient I/O when reading subsets of rows or columns. Pages: Within each column chunk, data is further divided into pages. Parquet uses pages to store column data more granularly, which helps optimize compression and read performance. Each page is typically a few megabytes in size, and Parquet stores statistics about the data in each page, making it easier to skip irrelevant pages during query execution. Columns and Row Groups : Parquet organizes data into row groups , which contain chunks of column data. A row group contains all the data for a subset of rows, but the data for each column is stored separately. This allows for efficient I/O when reading subsets of rows or columns. Columns and Row Groups row groups Pages : Within each column chunk, data is further divided into pages . Parquet uses pages to store column data more granularly, which helps optimize compression and read performance. Each page is typically a few megabytes in size, and Parquet stores statistics about the data in each page, making it easier to skip irrelevant pages during query execution. Pages pages Use Cases for Columnar Storage Columnar storage formats like Parquet are most effective in the following scenarios: Analytics-Heavy Workloads: If your workload involves a lot of analytical queries (e.g., calculating averages, filtering by certain columns), columnar formats will provide significant performance gains. Big Data Environments: Parquet is commonly used in distributed data environments where large datasets are stored in cloud data lakes (e.g., AWS S3, Google Cloud Storage). It works seamlessly with frameworks like Apache Spark and Presto, which are built to process data at scale. Data Warehousing: When designing data warehouses, storing data in Parquet allows you to run complex analytical queries efficiently while reducing storage costs due to Parquet’s high compression. Analytics-Heavy Workloads : If your workload involves a lot of analytical queries (e.g., calculating averages, filtering by certain columns), columnar formats will provide significant performance gains. Analytics-Heavy Workloads Big Data Environments : Parquet is commonly used in distributed data environments where large datasets are stored in cloud data lakes (e.g., AWS S3, Google Cloud Storage). It works seamlessly with frameworks like Apache Spark and Presto, which are built to process data at scale. Big Data Environments Data Warehousing : When designing data warehouses, storing data in Parquet allows you to run complex analytical queries efficiently while reducing storage costs due to Parquet’s high compression. Data Warehousing When Not to Use Columnar Storage While columnar storage offers significant advantages for read-heavy, analytical workloads, it may not be the best option for all use cases. For example, transactional systems that involve frequent, small updates to data (like an online store's transaction log) may perform better with row-based formats, which are optimized for write-heavy operations. In such cases, the overhead of reading and writing data in columnar format may outweigh its benefits. transactional systems Conclusion Parquet’s columnar storage model is what makes it a powerful tool for big data analytics. By organizing data by columns, Parquet allows for faster query performance, better compression, and more efficient aggregation. It’s designed to excel in environments where read-heavy workloads dominate and when your queries often target specific columns rather than entire datasets. In the next blog post, we’ll dive deeper into the file structure of Parquet, exploring how data is organized into row groups, pages, and columns to optimize both storage and retrieval. file structure Stay tuned for part 3: Parquet File Structure: Pages, Row Groups, and Columns . Parquet File Structure: Pages, Row Groups, and Columns

 All About Parquet Part 02 - Parquet's Columnar Storage Model

When building your application, one of the first questions you will probably ask yourself is: what database should I use to store my users' data? If you look up this question on the Internet, you will find not only that there are hundreds of different database management systems, but also that there are different categories of databases, such as SQL and NoSQL databases, key/value databases, row and column databases. In this article, you will learn what columnar databases are, when to use them, and their advantages and disadvantages. What Are Column-Oriented Databases? Columnar (or column-oriented) databases store tables of data by column rather than by row. They're often used in online analytical processing (OLAP) environments. For example, here's a simple table containing some information about usernames, their country, and their account status. | ID |  Name  | Active |  Country  |
|----|--------|--------|-----------|
|  1 | Andrii |      1 | UK        |
|  2 | Tom    |      0 | US        |
|  3 | Jack   |      1 | Canada    |
|  4 | Scott  |      1 | Australia | For columnar databases, the data would be stored as: 1234;AndriiTomJackScott;1011;UKUSCanadaAustralia; While traditional row-oriented databases present such tables as: 1Andrii1UK;2Tom0US;3Jack1Canada;4Scott1Australia Storing data differently drastically improves the performance of read operations. For example, performing aggregation functions such as averaging or summing will be many times faster on columnar databases. At the same time, a row-oriented database would perform better for write operations. Especially when inserting rows one at a time. They are perfect for transactional processing (OLTP). For example, consider a scenario where you want to create an analytical sales report. If you are using a row-oriented database, all the rows (chunks of data) have to be brought into memory and processed. Even if you select a particular column, the DBMS will first have to access a row such as 1Andrii1UK and then extract the required column from it. A column-oriented database would present data in chunks of columns, like AndriiTomJackScott, allowing much faster processing and read operations. Row-oriented databases prioritize transactional integrity and data normalization, while columnar databases focus on fast-read operations and data aggregation. Use Cases for Columnar Databases Columnar databases are perfect for reading and processing billions of data points. Here are some of the more common use cases: Log analysis Server logs are usually a pain to analyze, especially when there is a lot of information being logged from different processes. Columnar databases can store logs and later aggregate information such as the most common errors or their patterns. Business Analysis & Time Series Data Columnar databases are perfect for processing analytical data and building aggregated reports, and this is probably their most common use case. Columnar OLAPs are also commonly used in IoT applications and monitoring systems, which often deal with time-series data. Examples of Columnar DBMSs Many column-oriented databases share similarities, such as distributed processing, aggregation capabilities, and so on. However, many of them are designed for different purposes. Clickhouse Clickhouse is one of the most popular open-source real-time OLAP databases. Its speed and aggregation capabilities make it ideal for analytical purposes and it's used by organizations such as CERN, Akamai, and Bytedance. DuckDB DuckDB is an open-source in-process columnar DBMS. It also features fast reads and extensive aggregation functions, and has been described as the "SQLite of columnar databases." It’s perfect for applications dealing with logs or parsing CSV files. It’s also great for embedded programming, as it’s relatively small in size and fast. QuestDB QuestDB is also an open-source columnar database with a focus on efficient processing and aggregation of time series data. Final Thoughts There are dozens of different types of database management systems, and each one is tailored to specific use cases. Choosing the right database for your application is a good long-term solution that can save you the headache of migrating later. For example, if you're building analytical or log-processing applications, it's a good idea to opt for column-oriented databases, even if you don't store that much data initially. When building your application, one of the first questions you will probably ask yourself is: what database should I use to store my users' data? If you look up this question on the Internet, you will find not only that there are hundreds of different database management systems, but also that there are different categories of databases, such as SQL and NoSQL databases, key/value databases, row and column databases. In this article, you will learn what columnar databases are, when to use them, and their advantages and disadvantages. What Are Column-Oriented Databases? Columnar (or column-oriented) databases store tables of data by column rather than by row. They're often used in online analytical processing (OLAP) environments. For example, here's a simple table containing some information about usernames, their country, and their account status. | ID |  Name  | Active |  Country  |
|----|--------|--------|-----------|
|  1 | Andrii |      1 | UK        |
|  2 | Tom    |      0 | US        |
|  3 | Jack   |      1 | Canada    |
|  4 | Scott  |      1 | Australia | | ID |  Name  | Active |  Country  |
|----|--------|--------|-----------|
|  1 | Andrii |      1 | UK        |
|  2 | Tom    |      0 | US        |
|  3 | Jack   |      1 | Canada    |
|  4 | Scott  |      1 | Australia | For columnar databases, the data would be stored as: 1234;AndriiTomJackScott;1011;UKUSCanadaAustralia; 1234;AndriiTomJackScott;1011;UKUSCanadaAustralia; While traditional row-oriented databases present such tables as: 1Andrii1UK;2Tom0US;3Jack1Canada;4Scott1Australia 1Andrii1UK;2Tom0US;3Jack1Canada;4Scott1Australia Storing data differently drastically improves the performance of read operations. For example, performing aggregation functions such as averaging or summing will be many times faster on columnar databases. At the same time, a row-oriented database would perform better for write operations. Especially when inserting rows one at a time. They are perfect for transactional processing (OLTP). For example, consider a scenario where you want to create an analytical sales report. If you are using a row-oriented database, all the rows (chunks of data) have to be brought into memory and processed. Even if you select a particular column, the DBMS will first have to access a row such as 1Andrii1UK and then extract the required column from it. 1Andrii1UK A column-oriented database would present data in chunks of columns, like AndriiTomJackScott , allowing much faster processing and read operations. AndriiTomJackScott Row-oriented databases prioritize transactional integrity and data normalization, while columnar databases focus on fast-read operations and data aggregation. Use Cases for Columnar Databases Columnar databases are perfect for reading and processing billions of data points. Here are some of the more common use cases: Log analysis Server logs are usually a pain to analyze, especially when there is a lot of information being logged from different processes. Columnar databases can store logs and later aggregate information such as the most common errors or their patterns. Business Analysis & Time Series Data Columnar databases are perfect for processing analytical data and building aggregated reports, and this is probably their most common use case. Columnar OLAPs are also commonly used in IoT applications and monitoring systems, which often deal with time-series data. Examples of Columnar DBMSs Many column-oriented databases share similarities, such as distributed processing, aggregation capabilities, and so on. However, many of them are designed for different purposes. Clickhouse Clickhouse is one of the most popular open-source real-time OLAP databases. Its speed and aggregation capabilities make it ideal for analytical purposes and it's used by organizations such as CERN, Akamai, and Bytedance. DuckDB DuckDB is an open-source in-process columnar DBMS. It also features fast reads and extensive aggregation functions, and has been described as the "SQLite of columnar databases." It’s perfect for applications dealing with logs or parsing CSV files. It’s also great for embedded programming, as it’s relatively small in size and fast. QuestDB QuestDB is also an open-source columnar database with a focus on efficient processing and aggregation of time series data. Final Thoughts There are dozens of different types of database management systems, and each one is tailored to specific use cases. Choosing the right database for your application is a good long-term solution that can save you the headache of migrating later. For example, if you're building analytical or log-processing applications, it's a good idea to opt for column-oriented databases, even if you don't store that much data initially.

What Are Columnar Databases and When Do You Need to Use Them?

Auto-Increment Columns in Databases: A Simple Trick That Makes a Big Difference

Introduction In Python, you can use the pandas library to work with tabular data, and the core data type in pandas is the DataFrame. Sometimes, when working with DataFrame , you may need to convert rows to columns or columns to rows. Here is a simple example demonstrating how to achieve this using the pandas library. data Create Table First, let’s create a new example DataFrame. import pandas as pd

# create a new DataFrame
df = pd.DataFrame({
    'name': ['John', 'Mary', 'Peter'],
    'math': [80, 90, 70],
    'english': [70, 85, 90],
    'science': [75, 95, 80]
})

print(df)

# output:
    name  math  english  science
0   John    80       70       75
1   Mary    90       85       95
2  Peter    70       90       80 Convert Rows to Columns # Use the melt function to convert rows to columns
df_melt = pd.melt(df, id_vars=['name'], var_name='subject', value_name='score')

print(df_melt) In the code above, we simply use the function to transform each student's subject grades into a column, which gives us the following output. melt name  subject  score
0   John     math     80
1   Mary     math     90
2  Peter     math     70
3   John  english     70
4   Mary  english     85
5  Peter  english     90
6   John  science     75
7   Mary  science     95
8  Peter  science     80 Convert Columns to Rows Column-to-row transformation, also known as data unpivoting, can be achieved using the function in the pandas library. Here is an example code: pivot # Use the pivot function to pivot rows to columns.
df_pivot = df_meld.pivot(index='name', columns='subject', values='score')
print(df_pivot) subject  english  math  science
name
John          70    80       75
Mary          85    90       95
Peter         90    70       80 Thank you for taking the time to explore data-related insights with me. I appreciate your engagement. If you find this information helpful, I invite you to follow me or connect with me on or (@Luca_DataTeam). You can also catch glimpses of my personal life on , Happy exploring!👋 LinkedIn X Instagram

How to Convert Rows to Columns and Columns to Rows in Pandas DataFrame using Python

Slow query times in large datasets are a common headache in database management. offers a neat way out of this. It's a columnar storage engine that significantly speeds up data analytics. Typically, you can improve query performance in relational databases by adding appropriate indexes. MariaDB ColumnStore However, maintaining indexes is hard, especially with ad-hoc queries where you don't really know where indexes are going to be needed. ColumnStore eases this pain. It's as if you had an index on each column but without the hassle of creating and updating them. The price to pay? Well, inserts are not as fast as with , so this is not the best option for operational/transactional databases but rather for analytical ones. are very fast, though. InnoDB Bulk inserts There's plenty of about ColumnStore, so I won't go through all the details on how it works or how to deploy it on production. Instead, in this article, I'll show you how to try MariaDB ColumnStore on your computer using . online documentation Docker Prerequisites You'll need: The command line tool mariadb Docker Setting up MariaDB ColumnStore Run a container with MariaDB + ColumnStore: docker run -d -p 3307:3306 -e PM1=mcs1 --hostname=mcs1 --name mcs1 mariadb/columnstore This command runs a new Docker container using the official ColumnStore image with several specified options: : Starts a new Docker container. docker run : Runs the container in detached mode (in the background). -d : Maps port 3307 on the host (your computer) to port 3306 inside the container. This makes the database accessible on port on the host machine. -p 3307:3306 3307 : The environment variable specifies the primary database node ( ). -e PM1=mcs1 PM1 PM1 mcs1 : Sets the hostname of the container to . --hostname=mcs1 mcs1 : Names the container . --name mcs1 mcs1 : Specifies the Docker image to use, in this case, an image for MariaDB with the ColumnStore storage engine. mariadb/columnstore Provision ColumnStore: docker exec -it mcs1 provision mcs1 The command is used to interact with a running Docker container. This is what each option does: docker exec : Executes a command in a running container. docker exec : This option ensures the command is run in interactive mode with a terminal. -it (first occurrence): This is the name of the Docker container in which the command is to be executed. mcs1 This is the specific command being executed inside the container. is a script included in the Docker image that initializes and configures the MariaDB ColumnStore environment within the container. The argument is passed to the command to specify the host for the MariaDB server within the Docker container. provision mcs1 provision mcs1 provision Connect to the MariaDB server using the default credentials defined in the MariaDB ColumnStore Docker image: mariadb -h 127.0.0.1 -P 3307 -u admin -p'C0lumnStore!' Check that ColumnStore is available as a storage engine by running the following SQL sentence: SHOW ENGINES; Setting up a demo database Create the database and its InnoDB tables: operations CREATE DATABASE operations;

CREATE TABLE operations.doctors(
    id SERIAL PRIMARY KEY,
    name VARCHAR(200) NOT NULL CHECK(TRIM(name) != '')
) ENGINE=InnoDB;

CREATE TABLE operations.appointments(
    id SERIAL PRIMARY KEY,
    name VARCHAR(200) NOT NULL CHECK(TRIM(name) != ''),
    phone_number VARCHAR(15) NOT NULL CHECK(phone_number RLIKE '[0-9]+'),
    email VARCHAR(254) NOT NULL CHECK(TRIM(email) != ''),
    time DATETIME NOT NULL,
    reason ENUM('Consultation', 'Follow-up', 'Preventive', 'Chronic') NOT NULL,
    status ENUM ('Scheduled', 'Canceled', 'Completed', 'No Show'),
    doctor_id BIGINT UNSIGNED NOT NULL,
    CONSTRAINT fk_appointments_doctors FOREIGN KEY (doctor_id) REFERENCES doctors(id)
) ENGINE=InnoDB; Create the database and its ColumnStore table: analytics CREATE DATABASE analytics;

CREATE TABLE analytics.appointments(
    id BIGINT UNSIGNED NOT NULL,
    name VARCHAR(200) NOT NULL,
    phone_number VARCHAR(15) NOT NULL,
    email VARCHAR(254) NOT NULL,
    time DATETIME NOT NULL,
    reason VARCHAR(15) NOT NULL,
    status VARCHAR(10) NOT NULL,
    doctor_id BIGINT UNSIGNED NOT NULL
) ENGINE=ColumnStore; You can use the same database (or schema. They are in MariaDB) for both the InnoDB and ColumnStore tables if you prefer. Use a different name for the ColumnStore table if you opt for this alternative. synonyms Inserting demo data Insert a few doctors: INSERT INTO operations.doctors(name)
VALUES ("Maria"), ("John"), ("Jane"); Create a new file with the name with the following content: test_data_insert.py import random
import os
import subprocess
from datetime import datetime, timedelta

# Function to generate a random date within a given range
def random_date(start, end):
    return start + timedelta(days=random.randint(0, int((end - start).days)))

# Function to execute a given SQL command using MariaDB
def execute_sql(sql):
    # Write the SQL command to a temporary file
    with open("temp.sql", "w") as file:
        file.write(sql)
    # Execute the SQL command using the MariaDB client
    subprocess.run(["mariadb", "-h", "127.0.0.1", "-P", "3307", "-u", "admin", "-pC0lumnStore!", "-e", "source temp.sql"])
    # Remove the temporary file
    os.remove("temp.sql")

print("Generating and inserting data...")

# Total number of rows to be inserted
total_rows = 4000000
# Number of rows to insert in each batch
batch_size = 10000

# Possible values for the 'reason' column and their associated weights for random selection
reasons = ["Consultation", "Follow-up", "Preventive", "Chronic"]
reason_weights = [0.5, 0.15, 0.25, 0.1]

# Possible values for the 'status' column and their associated weights for random selection
statuses = ["Scheduled", "Canceled", "Completed", "No Show"]
status_weights = [0.1, 0.15, 0.7, 0.05]

# Possible values for the 'doctor_id' column and their associated weights for random selection
doctors = [1, 2, 3]
doctors_weights = [0.4, 0.35, 0.25]

# List of patient names
names = [f"Patient_{i}" for i in range(total_rows)]

# Insert data in batches
for batch_start in range(0, total_rows, batch_size):
    batch_values = []

    # Generate data for each row in the batch
    for i in range(batch_start, min(batch_start + batch_size, total_rows)):
        name = names[i]
        phone_number = f"{random.randint(100, 999)}-{random.randint(100, 999)}-{random.randint(1000, 9999)}"
        email = f"patient_{i}@example.com"
        time = random_date(datetime(2023, 1, 1), datetime(2024, 1, 1)).strftime("%Y-%m-%d %H:%M:%S")
        reason = random.choices(reasons, reason_weights)[0]
        status = random.choices(statuses, status_weights)[0]
        doctor_id = random.choices(doctors, doctors_weights)[0]

        # Append the generated row to the batch
        batch_values.append(f"('{name}', '{phone_number}', '{email}', '{time}', '{reason}', '{status}', {doctor_id})")

    # SQL command to insert the batch of data into the 'appointments' table
    sql = "USE operations;\nINSERT INTO appointments (name, phone_number, email, time, reason, status, doctor_id) VALUES " + ", ".join(batch_values) + ";"
    # Execute the SQL command
    execute_sql(sql)
    # Print progress
    print(f"Inserted up to row {min(batch_start + batch_size, total_rows)}")

print("Data insertion complete.") Insert 4 million appointments by running the Python script: python3 test_data_insert.py Populate the ColumnStore table by connecting to the database and running: INSERT INTO analytics.appointments (
    id,
    name,
    phone_number,
    email,
    time,
    reason,
    status,
    doctor_id
)
SELECT
    appointments.id,
    appointments.name,
    appointments.phone_number,
    appointments.email,
    appointments.time,
    appointments.reason,
    appointments.status,
    appointments.doctor_id
FROM operations.appointments; Run cross-engine SQL queries MariaDB ColumnStore is designed to run in a cluster of multiple servers. It is there where you see massive performance gains in analytical queries. However, we can also see this in action with the single-node setup of this article. Run the following query and pay attention to the time it needs to complete (make sure it queries the database): operations SELECT doctors.name, status, COUNT(*) AS count
FROM operations.appointments -- use the InnoDB table
JOIN doctors ON doctor_id = doctors.id
WHERE status IN (
    'Scheduled',
    'Canceled',
    'Completed',
    'No Show'
)
GROUP BY doctors.name, status
ORDER BY doctors.name, status; On my machine, it took around 3 seconds. Now modify the query to use the ColumnStore table instead (in the database): analytics SELECT doctors.name, status, COUNT(*) AS count
FROM analytics.appointments -- use the ColumnStore table
JOIN doctors ON doctor_id = doctors.id
WHERE status IN (
    'Scheduled',
    'Canceled',
    'Completed',
    'No Show'
)
GROUP BY doctors.name, status
ORDER BY doctors.name, status; It takes less than a second. Of course, you can speed up the first query by adding an index in this simplistic example, but imagine the situation in which you have hundreds of tables—it will become harder and harder to manage indexes. ColumnStore removes this complexity. Also published . here

Optimizing Database Performance with MariaDB ColumnStore

Strategies for Implementing Columnar Compression in Large PostgreSQL Databases

বড় পোস্টগ্রেএসকিউএল ডেটাবেসে কলামার কম্প্রেশন বাস্তবায়নের কৌশল

Strategien zur Implementierung der Spaltenkomprimierung in großen PostgreSQL-Datenbanken

Estrategias para implementar la compresión de columnas en grandes bases de datos PostgreSQL

Stratégies d'implémentation de la compression en colonnes dans les grandes bases de données PostgreSQL

बड़े PostgreSQL डेटाबेस में कॉलमर कम्प्रेशन लागू करने की रणनीतियाँ

大規模な PostgreSQL データベースに列圧縮を実装するための戦略

대규모 PostgreSQL 데이터베이스에서 열 형식 압축을 구현하기 위한 전략

Estratégias para implementar compactação colunar em grandes bancos de dados PostgreSQL

Стратегии реализации столбчатого сжатия в больших базах данных PostgreSQL

Büyük PostgreSQL Veritabanlarında Sütun Sıkıştırmasını Uygulama Stratejileri

Các chiến lược thực hiện nén cột trong cơ sở dữ liệu PostgreSQL lớn

在大型 PostgreSQL 数据库中实现列压缩的策略

https://www.youtube.com/watch?v=lQrpYlBHdlY&embedable=true&transcript=true Become a tech influencer with onboarding process! ! HackerNoon's 3-step Publish your technology story today Listen to the with , the COO of . full episode of Inside Startup Investing Linh Dao Smooke HackerNoon

How Do You Become a Technology Columnist? 

আপনি কিভাবে একটি প্রযুক্তি কলামিস্ট হয়ে উঠবেন?

Wie wird man Technologiekolumnist?

¿Cómo convertirse en columnista de tecnología?

Comment devenir chroniqueur technologique ?

आप एक प्रौद्योगिकी स्तंभकार कैसे बनें?

テクノロジーコラムニストになるにはどうすればよいですか?

기술 칼럼니스트가 되려면 어떻게 해야 하나요?

Como você se torna um colunista de tecnologia?

Как стать обозревателем технологий?

Nasıl Teknoloji Yazarı Olursunuz?

Làm thế nào để bạn trở thành một nhà báo chuyên mục công nghệ?

如何成为一名技术专栏作家？

How to become a Lightning Calculator by Anonymous is part of the HackerNoon Books Series. You can jump to any chapter in this book . How to Add Two Columns at Once. here How to Add Two Columns at Once. To the inexperienced it will be a difficult task to add two columns at once, but many of those who have daily practice in addition find it about as easy to add two columns as one. Say 99 and 50 are 149, and 6 are 155, and 10 and 50 are 215 and 3 are 218, and 12 are 230. Carry 2, and say 33 and 12 are 45, and 20 are 65, and 6 are 71, and 30 are 101, and 2 are 103, and 23 are 126. Much of the information here contained is compiled from W. D. Rowland’s valuable little volume, entitled “How to become expert with figures.” You can get this handy book by sending 25 cents in stamps to American Nation Co., Boston. About HackerNoon Book Series: We bring you the most important technical, scientific, and insightful public domain books. This book is part of the public domain. Anonymous (2021). How to become a lightning calculator. Urbana, Illinois: Project Gutenberg. Retrieved https://www.gutenberg.org/cache/epub/65078/pg65078-images.html This eBook is for the use of anyone anywhere at no cost and with almost no restrictions whatsoever. You may copy it, give it away or re-use it under the terms of the Project Gutenberg License included with this eBook or online at , located at . www.gutenberg.org https://www.gutenberg.org/policy/license.html

How to Add Two Columns at Once.

COLUMN

column.com

-- employees

Since n.d.

COMPANY RANKING

COLUMN

EVERGREEN INDEX #4778

Representing this company? Click here to claim and customize this page!

Column WIKI

Apply to get your Tech Company News Page live on HackerNoon.com today!

COLUMN

column.com

-- employees

Since n.d.

COMPANY RANKING

COLUMN

EVERGREEN INDEX #4778

RELATED COMPANIESRANK

STORIES ABOUT COLUMN

Representing this company? Click here to claim and customize this page!

Column WIKI

Apply to get your Tech Company News Page live on HackerNoon.com today!