BigFrames is an open source Python library offered by Google. BigFrames scales Python data processing by transpiling common Python data science APIs to BigQuery SQL. You can read more about BigFrames in the official BigFrames API reference and can refer to the public git repository for BigFrames.

Why use BigFrames with Apache Airflow?

Data pipelines are an essential component for modern analytics, machine learning, and AI. Apache Airflow is a popular open source platform to author, schedule, and monitor workflows. It is particularly useful for creating production data engineering pipelines. These pipelines are made up of nodes that depend on each other and form Directed Acyclic Graphs (DAGs for short). As an open source tool, there are many options for deploying Apache Airflow. In these examples, I use Google Cloud Composer, but the sample DAGs should work in any Airflow environment where you have configured a Google Cloud connection.

Unfortunately, common data engineering practices such as using pandas to transform data can overload the Airflow orchestration nodes, causing slower pipelines and delays or even out-of-memory crashes. BigFrames is “drop-in compatible” with pandas in many workloads by just changing the imports to bigframes.pandas. This keeps the data and the associated processing (transformations, joins, aggregations, AI enhancement, ML training and prediction, and validations) in the scalable BigQuery engine and out of the Airflow orchestration nodes where it causes problems.

The data engineering pipeline

This post will focus on the typical data engineering scenario of (1) getting data from some source — in this case the US Census, (2) cleaning it up, (3) validating the data is good quality, and (4) loading it into the final destination in your data warehouse / data lake. Since this pipeline will technically load data into BigQuery as temporary resources before transforming the data, this pipeline is what’s called an ELT pipeline rather than an ETL pipeline.

The combination of Airflow and BigFrames is very flexible. For example, my teammate Shobhit has written how BigFrames can be used to train an ML model. You can use his sample and the Airflow techniques I describe in this post to build a pipeline that trains an ML model, validates it, and then deploys it to production or does batch prediction with it to enrich a dataset.

In this post, I’ll focus specifically on the steps needed to run BigFrames. For complete, runnable DAG examples, please refer to the following samples in my code snippets GitHub repository:

• census_to_bigquery.py (demonstrates PythonOperator)
• census_to_bigquery_venv.py (demonstrates PythonVirtualenvOperator)

Getting the data from the US Census

The US Census publishes many different datasets. In this pipeline, you’ll load the County Population by Characteristics: 2020–2024 dataset, which are provided as CSV files over HTTPS.

BigQuery cannot read directly from HTTPS, so the first node in the DAG downloads the CSV file and uploads it to GCS. I use the BashOperator for simplicity.

GCS_BUCKET = "your-bucket-id"
GCS_LOCATION = f"gs://{GCS_BUCKET}/us-census/cc-est2024-agesex-all.csv"

download_upload = bash.BashOperator(
    task_id="download_upload",
    bash_command=f"""
    wget https://www2.census.gov/programs-surveys/popest/datasets/2020-2024/counties/asrh/cc-est2024-agesex-all.csv -P ~;
    gcloud storage cp ~/cc-est2024-agesex-all.csv {GCS_LOCATION}
    """,
)

For larger dataset there are many options for getting data into Google Cloud Storage. For example, the Cloud Storage Transfer Service can copy files from public HTTPS URLs to a Cloud Storage bucket and there are Apache Airflow operators for running a transfer service job.

Operators that can run BigFrames

BigFrames can run anywhere that can run Python and has access to Google Cloud resources. This includes the KubernetesPodOperator, DockerOperator, ExternalPythonOperator, PythonVirtualenvOperator, and PythonOperator. The KubernetesPodOperator is especially useful to provide an isolated environment running separately from the orchestration nodes, but this comes with additional complexity. Since this post uses bigframes, there is less reason to isolate from the orchestration nodes because the data processing happens in the BigQuery engine.

To simplify deployment, I recommend using the PythonOperator if you are able to install additional Python libraries such as the bigframes package to your Airflow environment. From the servers where Airflow is running, install bigframes:

pip install --upgrade bigframes==2.11.0

In Cloud Composer, configure the packages in the Composer product page in Google Cloud Console.

Use the PythonVirtualenvOperator if you need to install the bigframes package in an isolated environment.

Once BigFrames is installed, you can use the BigFrames pandas compatible module. Import the bigframes.pandas module in the Python operator you’ve chosen:

def callable_python():
    import bigframes.pandas as bpd
    # Operator logic will go here.

bf_to_gbq = PythonOperator(
    task_id="bf_to_gbq",
    python_callable=callable_python,
)

Recommended safeguards

I recommend first configuring a few BigFrames options to ensure the best performance:

• bpd.options.compute.maximum_result_rows: This setting acts as a safeguard, preventing inadvertent large downloads of data from BigQuery to your Airflow worker. If a BigFrames operation would result in more rows than this specified limit being downloaded, the operation will fail, thereby protecting your worker’s memory resources.
• bpd.options.bigquery.ordering_mode = “partial”: Activating this mode generally yields optimal performance for BigFrames operations by avoiding the pandas feature that causes the worst performance: a default, sequential ordering and index.
• bpd.options.bigquery.project: Depending on how you’ve configured your Google Cloud credentials, you may need to set this setting so that BigFrames knows which project to bill for the BigQuery queries it runs.

BigFrames uses a BigQuery session to store temporary resources. A BigQuery session automatically closes after 24 hours of inactivity, but I recommend putting a try/finally clause with bpd.close_session() to explicitly close the session to save on storage costs of the temporary resources bigframes creates.

bpd.options.bigquery.ordering_mode = "partial"
bpd.options.bigquery.project = "my-project-id"
bpd.options.compute.maximum_result_rows = 10_000

try:
    # Your logic here.
    ...
finally:
    bpd.close_session()

Reading a CSV with BigFrames

BigFrames provides several pandas-compatible methods to read data. Where possible, I recommend setting bpd.read_csv(…, engine=”bigquery”). By loading with the BigQuery engine, you can avoid having to read the file into memory and use BigQuery to parse the file(s) and load them into a temporary table.

# Inside the `try` block above.
GCS_BUCKET = "your-bucket-id"
GCS_LOCATION = f"gs://{GCS_BUCKET}/us-census/cc-est2024-agesex-all.csv"

df = bpd.read_csv(GCS_LOCATION, engine="bigquery")

Cleaning the data with BigFrames

The Census data has a “YEAR” column, but it’s not clear which year it refers to. You can use pandas-compatible methods to preprocess the data before writing it to the final destination. This can help cleanup the data to make it more useful and easier to understand.

# Perform preprocessing. For example, you can map some coded data
# into a form that is easier to understand.
df_dates = df.assign(
    ESTIMATE_DATE=df["YEAR"].case_when(
        caselist=[
            (df["YEAR"].eq(1), datetime.date(2020, 4, 1)),
            (df["YEAR"].eq(2), datetime.date(2020, 7, 1)),
            (df["YEAR"].eq(3), datetime.date(2021, 7, 1)),
            (df["YEAR"].eq(4), datetime.date(2022, 7, 1)),
            (df["YEAR"].eq(5), datetime.date(2023, 7, 1)),
            (df["YEAR"].eq(6), datetime.date(2024, 7, 1)),
            (True, None),
        ]
    ),
).drop(columns=["YEAR"])

# TODO(developer): Add additional processing and cleanup as needed.

Validating the data with BigFrames

One of the benefits of using BigQuery DataFrames in your operators is that it makes it easy to perform data validations before committing to write the data to its final destination.

# Note: cache() is optional, but if any of the preprocessing above is
# complicated, it hints to BigQuery DataFrames to run those first and
# avoid duplicating work.
df_dates.cache()

row_count, column_count = df_dates.shape
assert row_count > 0
assert column_count > 0
assert not df_dates["ESTIMATE_DATE"].hasnans

# TODO(developer): Add additional validations as needed.
# For example, conider comparing the distribution of data in DataFrame
# with the distribution of the data in the destination and alerting if there is a 
# significant shift or unknown outliers.

Writing to BigQuery

Writing the data to its final destination is as easy as calling bpd.DataFrame.to_gbq(). Or you can use other pandas-compatible I/O methods such as bpd.DataFrame.to_parquet() to write to GCS, instead.

BIGQUERY_DESTINATION = "your-gcp-project.airflow_demo.us_census_by_county2020_to_present"

# Now that you have validated the data, it should be safe to write
# to the final destination table.
df_dates.to_gbq(
    BIGQUERY_DESTINATION,
    if_exists="replace",
    clustering_columns=["ESTIMATE_DATE", "STATE", "COUNTY"],
)

A note on DAG construction

In this sample, I put all of the BigFrames code in a single operator. This has the benefit of all temporary resources getting cleaned up at the same time, but one loses out on Airflow’s ability to retry failed operations separately, which can allow for a more robust pipeline.

To prevent data from being cleaned up when the method operator finishes, call to_gbq(). If you don’t supply a name argument, one will be automatically assigned. You can then use Airflow’s XCom feature to pass the table ID along to the next task. Make sure to create a node that cleans up the table that is created when the DAG finishes or else the table may live for up to 7 days before being automatically cleaned up by BigQuery.

def preprocess(task_instance):
    # Imports and settings go here.

    try:
        df = bpd.read_csv(GCS_LOCATION, engine="bigquery")
        # Preprocessing code goes here.
        task_instance.xcom_push(key="census_preprocessed_table", value=df.to_gbq())
    finally:
        bpd.close_session()

def validate_and_write(task_instance):
    # Imports and settings go here.

    try:
        # Get the table ID from the previous step.
        bigquery_source = task_instance.xcom_pull(
            task_ids="bf_preprocess",
            key="census_preprocessed_table",
        )
        df = bpd.read_gbq(bigquery_source)
        # Validation code goes here.
        df.to_gbq(bigquery_destination, if_exists="replace")
    finally:
        bpd.close_session()

bf_preprocess = PythonOperator(
    task_id="bf_preprocess",
    python_callable=preprocess,
)

bf_validate_and_write = PythonOperator(
    task_id="bf_validate_and_write",
    python_callable=validate_and_write,
)

cleanup_preprocess_table = BigQueryDeleteTableOperator(
    task_id="cleanup_preprocess_table",
    deletion_dataset_table="{{ task_instance.xcom_pull(task_ids='bf_preprocess', key='census_preprocessed_table' }}",
    # Always execute, even if the previous task failed.
    # https://stackoverflow.com/a/44441890/101923
    trigger_rule="all_done",
)

download_upload >> bf_preprocess >> bf_validate_and_write >> cleanup_preprocess_table
bf_preprocess >> cleanup_preprocess_table

For a detailed example, checkout my code snippets GitHub repository:

• census_to_bigquery_split.py

Conclusion

The integration of BigQuery DataFrames with Apache Airflow provides data engineers with the means to construct more robust, scalable, and efficient data pipelines. By performing data transformations and validations directly within BigQuery, you gain several significant advantages:

• Enhanced Safety: Minimize the risk of memory overloads on Airflow workers and execute crucial data validations before any impact on production systems.
• Greater Flexibility: Harness the extensive capabilities of BigQuery for complex transformations, unburdened by the memory constraints of individual Airflow workers.
• Familiar Workflow: Data engineers and analysts already proficient with pandas will find BigFrames a natural and intuitive extension for interacting with BigQuery data.

This approach effectively delegates the intensive computational tasks to your data warehouse, allowing Airflow to focus on its core strength: precise and reliable workflow orchestration. Happy pipelining!

The BigFrames team would love to hear from you. If you would like to reach out, please send an email to: bigframes-feedback@google.com or by filing an issue at the open source BigFrames repository. To receive updates about BigFrames, subscribe to the BigFrames email list.

Creating a production-ready data pipeline with Apache Airflow and BigFrames was originally published in Google Cloud - Community on Medium, where people are continuing the conversation by highlighting and responding to this story.

Luckily, my work with the Google BigQuery team had introduced me to a wealth of public datasets, and one in particular caught my eye: the NOAA Global Summary of the Day (GSOD) database. This treasure trove of information contains daily weather records from thousands of stations worldwide, tracking everything from temperature and rainfall to wind speed. It’s like a time machine for weather, letting you see what conditions were like on any given day in the past hundred years or so.

It’s important to note that this project is a personal side endeavor, developed on my own time and hosted using my own resources. Any information shared here is my own work and doesn’t necessarily reflect the views or endorsements of my employer.

I dove into the data, crafting a query to pull weather records from stations near our venue. After some analysis, I had a clear picture of what to expect. For our wedding date, the average high was 37°F (3°C), and the average low was 18°F (-8°C). But since weather can be unpredictable, I also looked at the range of likely outcomes: about half the time, the low temperature fell between 10°F (-12°C) and 27°F (-3°C). This helped us prepare for the possibility of a chilly — or even snowy — day.

This data helped us make smart decisions, like choosing the right attire and preparing for the possibility of snow. And sure enough, our wedding day was a beautifully temperate — but still snowy — wonderland! After that, I started using the tool for all kinds of planning. For example, when my wife and I were packing for our honeymoon in Spain and France, historical weather data helped us decide what to bring. But for a while, it remained a personal tool.

I realized this could be helpful for a lot more people. Anyone planning an outdoor event, or simply curious about their local weather history, could benefit. That’s why I teamed up with Mario Torres Jr., a former apprentice of mine from Google. He’s been instrumental in helping me make this tool more accessible. You can find him on LinkedIn: https://www.linkedin.com/in/mario-torres-jr. Together, we’re working to make it easier for everyone to explore their local weather history.

Curious about the weather history in your area? Whether you’re planning a wedding, a vacation, or even a garden, Overcast Data can help. Visit overcastdata.com to explore historical weather patterns and make your plans with confidence. What surprises will you uncover? We’d love to hear how you use the tool — share your stories with us!

Set the bigframes.pandas.options.bigquery.ordering_mode = "partial" option to prevent BigQuery DataFrames from creating a deterministic ordering and sequential index. Later in this post, I show an example of where this has a 4,000,000x speed up in terms of bytes scanned.

Without this option set, BigQuery DataFrames creates a sequential index over all rows to mimic pandas behavior. On large tables this can get expensive, as it forces a full table scan, unless row and column filters are provided as arguments to bpd.read_gbq. Partial ordering mode allows BigQuery DataFrames to push down many more row and column filters. On large clustered and partitioned tables, this can greatly reduce the number of bytes scanned and computation slots used.

A brief primer on indexes in pandas and BigQuery DataFrames

In pandas_gbq, when you download a BigQuery table as a DataFrame, pandas assigns a default index to the rows base on the order they were downloaded.

import pandas_gbq

df = pandas_gbq.read_gbq(
  "bigquery-public-data.ml_datasets.penguins",
  project_id=my_project)
df

                                       species     island  culmen_length_mm  culmen_depth_mm  flipper_length_mm  body_mass_g     sex
0          Adelie Penguin (Pygoscelis adeliae)      Dream              36.6             18.4              184.0       3475.0  FEMALE
1          Adelie Penguin (Pygoscelis adeliae)      Dream              39.8             19.1              184.0       4650.0    MALE
2          Adelie Penguin (Pygoscelis adeliae)      Dream              40.9             18.9              184.0       3900.0    MALE
3    Chinstrap penguin (Pygoscelis antarctica)      Dream              46.5             17.9              192.0       3500.0  FEMALE
4          Adelie Penguin (Pygoscelis adeliae)      Dream              37.3             16.8              192.0       3000.0  FEMALE
..                                         ...        ...               ...              ...                ...          ...     ...
339        Adelie Penguin (Pygoscelis adeliae)  Torgersen              38.8             17.6              191.0       3275.0  FEMALE
340        Adelie Penguin (Pygoscelis adeliae)  Torgersen              40.9             16.8              191.0       3700.0  FEMALE
341        Adelie Penguin (Pygoscelis adeliae)  Torgersen              39.0             17.1              191.0       3050.0  FEMALE
342        Adelie Penguin (Pygoscelis adeliae)  Torgersen              40.6             19.0              199.0       4000.0    MALE
343        Adelie Penguin (Pygoscelis adeliae)  Torgersen              37.3             20.5              199.0       3775.0    MALE

[344 rows x 7 columns]

The index for a particular row doesn’t change, even if you apply a row filter to that DataFrame.

df[df["species"].str.contains("Chinstrap")]

                                       species island  culmen_length_mm  culmen_depth_mm  flipper_length_mm  body_mass_g     sex
3    Chinstrap penguin (Pygoscelis antarctica)  Dream              46.5             17.9              192.0       3500.0  FEMALE
6    Chinstrap penguin (Pygoscelis antarctica)  Dream              46.9             16.6              192.0       2700.0  FEMALE
7    Chinstrap penguin (Pygoscelis antarctica)  Dream              50.5             18.4              200.0       3400.0  FEMALE
8    Chinstrap penguin (Pygoscelis antarctica)  Dream              49.5             19.0              200.0       3800.0    MALE
13   Chinstrap penguin (Pygoscelis antarctica)  Dream              47.0             17.3              185.0       3700.0  FEMALE
..                                         ...    ...               ...              ...                ...          ...     ...
115  Chinstrap penguin (Pygoscelis antarctica)  Dream              48.5             17.5              191.0       3400.0    MALE
119  Chinstrap penguin (Pygoscelis antarctica)  Dream              46.4             17.8              191.0       3700.0  FEMALE
120  Chinstrap penguin (Pygoscelis antarctica)  Dream              47.5             16.8              199.0       3900.0  FEMALE
121  Chinstrap penguin (Pygoscelis antarctica)  Dream              48.1             16.4              199.0       3325.0  FEMALE
123  Chinstrap penguin (Pygoscelis antarctica)  Dream              55.8             19.8              207.0       4000.0    MALE

[68 rows x 7 columns]

Why does pandas do this? A big reason is that in pandas, when you combine two objects, you are actually joining on an index.

s1 = pd.Series([1, 2, 3], index=[0, 2, 4], dtype="Int64")
s2 = pd.Series([4, 5, 6], index=[0, 1, 2], dtype="Int64")

# Align the two Series by their respective indexes and then add.
s1 + s2
# 0       5
# 1    <NA>
# 2       8
# 4    <NA>
# dtype: Int64

By preserving the index after the filter, pandas can still join the results back to the original DataFrame. In a SQL system like BigQuery, to do the same one needs a unique key to join by. pandas provides such a key implicitly for all objects. (Aside: it is possible to create a pandas object where the index is not unique. The behavior when joining on the index can get a bit unpredictable in this case.)

As a pandas-like API, BigQuery DataFrames (bigframes) also creates a sequential index based on the row order if no other index, such as a primary key, is available. Just like pandas, BigQuery DataFrames uses this index to implicitly join DataFrame and Series objects.

To get determinism, BigQuery DataFrames hashes all columns to provide a total ordering of the rows1. To get a sequential index, BigQuery DataFrames uses the ROW_NUMBER() analytic operation. Notably, in BigQuery DataFrames and pandas, the index values remain stable, even after row filters are applied.

s = pd.Series([1, 2, 3, 4, 5, 6], dtype="Int64")
s[s % 2 == 0]
# 1    2
# 3    4
# 5    6
# dtype: Int64

This allows two objects with different filters to be implicitly joined together, matching on the same rows as if the filter hadn’t been applied.

s = pd.Series([1, 2, 3, 4, 5, 6], dtype="Int64")
s[(1 < s) & (s < 6)]
# 1    2
# 3    4
# 5    6
# dtype: Int64

s[s % 2 == 0] + s[(1 < s) & (s < 6)]
# 1       4
# 2    <NA>
# 3       8
# 4    <NA>
# 5    <NA>
# dtype: Int64

While useful in some contexts, creating a sequential index is an expensive operation, especially on clustered and partitioned tables. The hash for deterministic ordering means that column filters don’t work as expected and the analytical ROW_NUMBER() operation over the whole table means that row filters don’t work as expected.

Partial ordering mode and NULL indexes

To prevent unexpected full table scans, BigQuery DataFrames warns with a bigframes.exceptions.DefaultIndexWarning when reading from partitioned and clustered tables with no index_col or filters defined. For tables without a suitable set of columns to use as the index_col, as of BigQuery DataFrames (bigframes) version 1.7.0, you can set index_col=bigframes.enums.DefualtIndexKind.NULL in read_gbq().

When setting this option, the analytic ROW_NUMBER operation is removed, allowing row filters to be pushed down. This has the potential to save you a lot of bytes scanned in workloads on clustered and partitioned tables. The tradeoff is that now unrelated DataFrame and Series objects can no longer be implicitly joined. In some cases, BigQuery DataFrames can determine that two objects are derived from the same table expression, but this isn’t always possible.

As of BigQuery DataFrames (bigframes) version 1.12.0, you can also disable a default ordering by setting the bigframes.pandas.options.bigquery.ordering_mode = “partial” option. This turns off default sequential indexes and also sets the default index kind to NULL. This allows both row filters and column filters to work as expected.

Trying it out

Query the last few days of data from the bigquery-public-data.pypi.file_downloads table, which is partitioned by the timestamp column and clustered by the project column with ordering_mode = “partial”. Do not try this with the default sequential index, as the table is 375+ TB total logical bytes large and growing.

import datetime

import bigframes.exceptions
import bigframes.pandas as bpd

# IMPORTANT: use partial ordering mode to allow filters to work as expected.
bpd.options.bigquery.ordering_mode = "partial"

# Show a preview of the previous day's downloads.
pypi = bpd.read_gbq("bigquery-public-data.pypi.file_downloads")
last_1_days = (
    datetime.datetime.now(datetime.timezone.utc)
    - datetime.timedelta(days=1)
)
bigframes_downloads = pypi[
    (pypi["timestamp"] > last_1_days)
    & (pypi["project"] == "bigframes")
]
bigframes_downloads[["timestamp", "project", "file"]].peek()

                   timestamp    project                                               file
0  2025-03-12 10:45:41+00:00  bigframes  {'filename': 'bigframes-0.22.0-py2.py3-none-an...
1  2025-03-12 10:34:06+00:00  bigframes  {'filename': 'bigframes-0.22.0-py2.py3-none-an...
2  2025-03-12 10:39:20+00:00  bigframes  {'filename': 'bigframes-0.22.0-py2.py3-none-an...
3  2025-03-12 10:38:37+00:00  bigframes  {'filename': 'bigframes-1.40.0-py2.py3-none-an...
4  2025-03-12 10:42:25+00:00  bigframes  {'filename': 'bigframes-1.40.0-py2.py3-none-an...

If you remove bpd.options.bigquery.ordering_mode = "partial" (please don’t), then the query BigQuery DataFrames generates processes 376.27 TB. But with this option set, the query BigQuery DataFrames generated only processes 84.8 MB processed. This is 4,000,000+ times speedup in terms of cost for on-demand queries!

Check out the Analyzing package downloads from PyPI with BigQuery DataFrames notebook for a more detailed look at these data using ordering_mode = “partial”. Also, learn more about BigQuery DataFrames and its features at the Use BigQuery DataFrames guide.

Share your feedback

Is this feature helpful to you? Is there some functionality you need that is missing? The BigFrames team would love to hear from you. If you would like to reach out please send an email to: bigframes-feedback@google.com or by filing an issue at the open source BigFrames repository. To receive updates about BigFrames, subscribe to the BigQuery DataFrames aka BigFrames email list.

1. If the table contains duplicate rows, the order of duplicate rows isn’t deterministic, but since the data is identical, the order of the identical rows won’t affect the results. BigQuery DataFrames performs a double hash to prevent collisions.

Copyright 2024. Released under Creative Commons Attribution License. Originally posted September 4, 2024 to https://friendliness.dev/2024/09/04/bigframes-ordering-mode-partial/.

Introducing partial ordering mode for BigQuery DataFrames (bigframes) was originally published in Google Cloud - Community on Medium, where people are continuing the conversation by highlighting and responding to this story.

Definitions

As a distributed community, there isn’t a definitive definition of “dweb”. In our discussion at Offline Camp, we identified several common features of “dweb” apps, such as the ability to distribute and verify web content no matter how you obtained it. With the web, you need to be “online”, at least initially, to download the content. With the dweb, no matter how you obtain a document, whether from a friend on your local network or copied from a portable hard drive, you’ll still be able to verify that the document is the one you expected and was not tampered with.

Overwhelming vocabulary

It’s difficult to know how to get started with the dweb. A beginner is confronted with many confusing terms, including “dweb” itself. In an introductory article, a beginner may encounter most of the following terms:

• DWeb
• Distributed
• Decentralized
• P2P
• Server
• Client
• Peer
• Seed
• Pub
• Pub/Sub
• Node
• Hash
• Content addressable
• Local-first
• Public key
• Wallet
• Blockchain
• Data sovereignty
• Discovery
• Federated
• Smart Contract

But most developers don’t care about these details. What they do care about is user experience. By discussing the benefits of dweb technologies first, a developer can then be motivated to learn the necessary details to use it.

Resilience

Resilience is a benefit that most agreed is important. In this way, dweb technologies can be viewed as a digital “seat belt”. They provide the most value in situations where traditional web apps fail.

Even in western cities, network connectivity is not guaranteed. For example, a web app may fail in the subway or during a disaster that affects network infrastructure. Likewise, it is an economic reality that apps shut down due to acquisition or lack of funding. A dweb app can also protect an end-user’s access to their data in both of these situations.

Efficiency

By removing round-trip access to data, dweb technologies can save on network costs. An efficient use of resources can even save energy, which is becoming an increasingly important factor in light of global climate change.

Access

As Nolan Lawson describes in Decentralization is Not Enough, these benefits should not be limited to the nerds of the world. As dweb technologies are built, they need to be designed and documented in ways that are more accessible to those of us without PhDs in distributed systems.

Effective Beginner-Friendly Messaging for Dweb was originally published in Offline Camp on Medium, where people are continuing the conversation by highlighting and responding to this story.

I have the following goals for my personal projects:

• I want the end-product to be approachable.
• I want it to feel effortless for me to make progress on the project.
• I want the project to remain useful for a long time.

In my lightning talk at Offline Camp Oregon 2019, I share how these goals led me towards 1990s aesthetics.

These design principles have guided several of my recent projects. For example, my pixel art emoji stickers are clearly inspired by the 90s aesthetic.

Likewise, I craft the HTML for my personal website by hand, similar to what folks did in the early web.

In the next sections, I will explain how I was drawn towards the 90s aesthetics. In what ways do they support the goals that I outlined?

Approachable

I write blog posts for people to read. I don’t want to distract the reader with banners, ads, or even navigation. Forcing myself to use handwritten HTML helps me feel less temptation to add unnecessary fluff.

The reason that I draw cute pixel-art emoji is so that my friends and family members can enjoy and use them.

It's been argued that pixel art is not approachable to the modern audience. In my experience, however, even the generations younger than me have developed an appreciation for it. Minecraft and indie games have kept the style alive, despite it no longer being necessary due to hardware restrictions.

Effortless

It's clear that some projects require more mental energy to progress than others. I find it quite easy — relaxing even — to pull out my iPad and draw, whereas making progress on a computer programming project requires few distractions for long stretches of time.

Even as an artist, I struggle with a blank canvas. Having too many options causes me to freeze up. It takes mental energy to decide what to do next. A common strategy to overcome this is to boost creativity by using design constraints.

Why make pixel art? It's a restriction, based on discrete mathematics and the past. Restriction makes sharing my art a little less intimidating. There's only so far you can take 16 by 16 pixels.

Why make a blog post with static files? It's a restriction. I can spend less time configuring and more time writing.

Future-proof

In stepping back to a 90s aesthetic, I'm developing works that would have been more-or-less usable on the software of 25 years ago. It's my hope that this means my HTML, GIFs, and PNG images will be usable for at least another 25 years.

Modern technology

Dat and IPFS are ideally suited to sharing static files. This means that it's easy to share my site with modern peer-to-peer technologies.

I'm hopeful that as these technologies develop and gain popularity, the chance that my work disappears will go down. So long as someone finds what I've made useful, it can stay alive on the distributed web.

Editor’s Note: Participants at Offline Camp Oregon 2019 had diverse backgrounds and interests, ranging far beyond the Offline First approach that we came together to discuss. Through short passion talks, campers shared with us some of the hobbies, projects, and technologies that excite them. We’re sharing a taste of that passion with you here as a preview to our upcoming events.

90s Aesthetics in Modern Tech was originally published in Offline Camp on Medium, where people are continuing the conversation by highlighting and responding to this story.

TL;DR

Upgrade to the latest google-cloud-bigquery and google-cloud-bigquery-storage packages to download query results to a DataFrame 4.5 times faster compared to the same method with version 1.16.0. If you aren't using the BigQuery Storage API yet, use it to download your query results 15 times faster compared to the BigQuery API. (31 times, if you don’t mind using the default Arrow to pandas conversion.)

This speed-up also works with the pandas-gbq library. Update, the google-cloud-bigquery and google-cloud-bigquery-storage packages and install pyarrow, and set the use_bqstorage_api parameter to True.

Code samples

To use the faster method to download large results, use the BigQuery Storage API from your Python programs or notebooks.

Before you begin

• Enable the BigQuery Storage API: https://console.cloud.google.com/apis/library/bigquerystorage.googleapis.com
• Install the latest versions of the necessary Python packages (including dependencies).
• Steps for conda:

conda install --channel conda-forge 'google-cloud-bigquery>=1.17.0' \
  'google-cloud-bigquery-storage>=0.7.0' \
  'pandas-gbq>=0.10.0'

• Steps for pip:

pip install --upgrade google-cloud-bigquery
pip install --upgrade google-cloud-bigquery-storage
pip install --upgrade pyarrow
pip install --upgrade google-cloud-core
pip install --upgrade google-api-core[grpcio]

From a notebook environment, the steps are the same, just prepend ! to call to the shell. For example:

!pip install --upgrade google-cloud-bigquery

Using pandas-gbq

import pandas_gbq

sql = "SELECT * FROM `bigquery-public-data.irs_990.irs_990_2012`"

# Use the BigQuery Storage API to download results more quickly.
df = pandas_gbq.read_gbq(sql, use_bqstorage_api=True)

Using the BigQuery client library

import google.auth
from google.cloud import bigquery
from google.cloud import bigquery_storage_v1beta1

# Create a BigQuery client and a BigQuery Storage API client with the same
# credentials to avoid authenticating twice.
credentials, project_id = google.auth.default(
    scopes=["https://www.googleapis.com/auth/cloud-platform"]
)
client = bigquery.Client(credentials=credentials, project=project_id)
bqstorage_client = bigquery_storage_v1beta1.BigQueryStorageClient(
    credentials=credentials
)
sql = "SELECT * FROM `bigquery-public-data.irs_990.irs_990_2012`"

# Use a BigQuery Storage API client to download results more quickly.
df = client.query(sql).to_dataframe(bqstorage_client=bqstorage_client)

What’s next

• Read the BigQuery Storage API reference documentation.
• Read the official how-tos for using the BigQuery Storage API with pandas.

New features

In addition to faster performance, google-cloud-bigquery package version 1.17.0 adds a RowIterator.to_arrow() method to download a table or query results as apyarrow.Table object.

Arrow provides a cross-language standard for in-memory, column-oriented data with a rich set of data types. It is fast to create a pandas DataFrame from an Arrow Table with the method. With the fletcher library, the Arrow Table can be used directly as the backing data structure of a pandas extension array.

Better performance

We tested the performance of downloading BigQuery table data to pandas DataFrame and Arrow Tables by sampling the bigquery-public-data.new_york_taxi_trips.tlc_green_trips_* tables.

We then timed how long it took to download these data to a pandas DataFrame from a Google Compute Engine n1-standard-8 (8 vCPUs, 30 GB memory) machine. We used the following methods:

• A: to_dataframe() - Uses BigQuery tabledata.list API.
• B: to_dataframe(bqstorage_client=bqstorage_client), package version 1.16.0 - Uses BigQuery Storage API with Avro data format.
• C: to_dataframe(bqstorage_client=bqstorage_client), package version 1.17.0 - Uses BigQuery Storage API with Arrow data format.
• D: to_arrow(bqstorage_client=bqstorage_client).to_pandas(), package version 1.17.0 - Uses BigQuery Storage API with Arrow data format.

The speedup is quite stable across data sizes. Using the BigQuery Storage API with the Avro data format is about a 3.5x speedup over the BigQuery tabledata.list API. It’s about a 15x speedup to use the to_dataframe() method with the Arrow data format, and a 31x speedup to use the to_arrow() method, followed by to_pandas() with the BigQuery Storage API.

In conclusion, the fastest way to get a pandas DataFrame from BigQuery is to call RowIterator.to_arrow(bqstorage_client=bqstorage_client).to_pandas(). The reason for this difference is that to_dataframe() converts each message into a DataFrame and then concatenates them into a single DataFrame at the end. Whereas to_arrow() converts each message to a RecordBatch and creates a Table at the end. The difference in time is likely because pandas.concat(dfs) can make copies, where as pyarrow.Table.from_batches() doesn't make any copies. Also, pyarrow.Table.to_pandas() is often zero-copy.

Limitations

This BigQuery Storage API does not have a free tier, and is not included in the BigQuery Sandbox. Because of these limitations, you are required to have a billing account to use this API. See the BigQuery Storage API pricing page for details.

The BigQuery Storage API does not yet read from small anonymous query results tables.

There are restrictions on the ability to reorder projected columns and the complexity of row filter predicates. Currently, filtering support when serializing data using Apache Avro is more mature than when using Apache Arrow.

Originally published at https://friendliness.dev on July 29, 2019.

Announcing google-cloud-bigquery Version 1.17.0 was originally published in Google Cloud - Community on Medium, where people are continuing the conversation by highlighting and responding to this story.

Alexandra and I attended Pixel Art Park 5 in Tokyo on December 9, 2018. I loved seeing so many pixel art pieces, chiptunes, and retro-style video games in one place and meeting other artists.

We got to the venue shortly before the festival opened and joined a long line to enter! Organizers announced (in Japanese) that we needed ¥ 1,000 in cash to pay for a ticket. A few minutes past 11 am, we all streamed into the building.

Inside, Alexandra and I explored three stories worth of art, music, games, and merch. The ground floor was quite crowded, so we went up to the second floor shortly after buying our tickets and commemorative t-shirt!

In the basement floor, we played demos of indie retro-style video games and enjoyed the “8-bit balloon art”.

The booths on the ground floor included several illustrators and a station for buying and constructing fuse bead art. Chiptune music played in the background while the attendees shopped and crafted.

The top floor was the largest and where we spent the most time. We strolled through the several rows of booths, while musicians played live on-stage. I enjoyed meeting the artists and developers in-person whose worked I’ve “liked” online.

We bought t-shirts, prints, postcards, and pins from some of our favorite artists. Also for sale were books, handkerchiefs, CDs, and voxel sculptures. A few of the booths were so popular that people queued for many minutes to meet the artist and buy merchandise. I think people even purchased a separate ticket to meet Waneella in the morning before her booth opened to everyone in the afternoon.

In addition to selling items, the folks at the YMCK booth drew free pixel art portraits.

I really like how cute Alexandra’s portrait turned out!

We played several games. Alexandra and I both attempted to fish as much garbage as we could out of the river in the Hotel 337 minigame.

I met the developers of dotpict, my favorite pixel art editor for mobile phones, and played their rhythm-based minigame.

We also met the developer of PICO-8 and Voxatron. It was crowded, but I enjoyed playing Voxatron on the Looking Glass volumetric display and Feed the Ducks for PICO-8.

Feed the Ducks was very cute and funny. Alexandra was even inspired to try making her own game because of it.

After lunch, we came back to listen to more music and talks. We caught the last minute of Waneella’s talk. After that a few artists did some live drawing of a Christmas tree. Even though I couldn’t understand most of the banter, it was very entertaining to watch.

We also stayed to listen to some more live music. Our favorite was Robotprins, which mixed guitar with chiptunes. We were uncertain when we saw the guitar, but it worked really well together with the beeps and bloops!

Pixel Art Park as an English Speaker

I wished my Japanese skills were better a few times throughout the day. When we first lined up to meet Waneella, we figured out that we needed a separate ticket, but I didn’t know enough Japanese to know when or where we could have bought one. Similarly, we missed much of Waneella’s talk because I couldn’t understand the printed schedule.

My interactions with the artists weren’t as in-depth as I would have liked, partly because of the language barrier. In some cases I was able to communicate that I like their game / app / art and had already installed it or followed them. I said かわいい (cute) for many of the pieces we saw, and the artists did seem to appreciate that.

Conclusions

Even if I were fluent at Japanese, I think this format isn’t the best for in- depth conversations and trading techniques. I would love for there to be a pixel art “unconference” for small group conversations before or after a future Pixel Art Park. There were so many talented people in one place, and I’d love to learn more from them.

I was encouraged to see a crowd of people expressing their love for pixel art. It was amazing to see how many talented artists there are, as well as how many fans (including myself) who are willing to buy physical items from artists.

Overall, I had a lot of fun at Pixel Art Park 5. I enjoyed playing the retro- style video games, and we filled a shopping bag with pieces purchased from artists that I admire. I hope to return to Pixel Art Park someday.

Originally published at www.timswast.com on December 30, 2018.

We’ve enjoyed these a year together. Now, I’d like to share our fox emoji pack (ZIP) with everyone.

www.timswast.com/blog/2018...

I imagine a whole ecosystem of cyborgs and robots, living off of sunlight and the mineral-rich asteroids. For the indoor tiles, I imagine a punk ranch hand keeping an eye on the action through a hologram.

Planning

This was my third year participating in octobit (see: 2017, 2016). I’ve enjoyed each year and am very grateful to Bruno, the organizer, for encouraging us all to participate.

This year was different from the previous years in a couple ways:

• Previously there had been no restrictions on style, other than pixel art (and a suggested palette). This year, the organizer suggested we draw in isometric perspective.
• Instead of a theme for each day, we could decide to draw each theme in any order.
• Optionally, attempt to make our isometric tiles fit together as a whole.

I followed Bruno’s advice to plan ahead and sketched out a few ideas. Eventually, I decided on a “space ranching” theme.

Space ranching has been on my mind for over 5 years, and octobit felt like a good opportunity to make some progress on the idea.

Posting

This year, I posted my artworks to my website first and then syndicated to social media (a.k.a. IndieWeb POSSE) with a little help from micro.blog.

In 2016 and 2017, I posted to Twitter first and then all the various social networks. It was a daily chore, and it felt like it! This year felt much better.

I didn’t post every day, which contributed to the more relaxed feeling. There were days when I was able to draw ahead and queue up a few artworks. This meant that I could have a few less productive days without falling behind.

Practicing

A few things about the artwork didn’t go as well as I planned.

Scale

I drew the vehicles and planets at the same resolution, this meant that they seemed the same size. In isometric projection, each pixel represents the same amount of distance, no matter how far away it is from the viewer. This meant that if I wanted a cohesive drawing, either my planets should have been much larger or the vehicles should have been much smaller.

The final collage feels crowded. Space shouldn’t be like that. There should be a lot of whitespace between the objects to give a better sense of the overall scale. Right now it feels like a collection of toys.

Lighting

In each work, imagined light coming from the bottom-right, but I only did a so-so job of shading. I think the shading probably turned out best in the planets.

These were much easier to shade because they are basically spheres. I want to get better at shading shiny spaceships (the space shuttle is too matte)

and woolly creatures (the sheep looks speckled, not wooly)

I hope to study more before the next octobit, using tools like Pixel Art Academy to level-up my skills.

Participating

The main reason I join octobit each year is to be a part of the online pixel art community. Other artists there are friendly and making amazing artworks. Some of the other pixel artists whose work I enjoyed this past October include:

• Bruno’s isometric “Porco” the space pig comics,
• SalamiChild’s medieval tiles (I love how SalamiChild does so much with so few pixels),
• Johan Peitz’s cowboys and aliens PICO-8 cart,
• Chris E’s cute pixel town,
• The Artist Julian’s breathtaking environments,
• Elena Nazaire’s detailed autumn scenes and isometric art,
• Wolve_Rain’s beautiful illustrations,
• and marta.salamon’s lovely botanical witches.

Despite seeing my art alongside these great artists, this year felt lonelier than past years. I should have commented more on other artists’ posts throughout the month. I also wonder if I should engage in some of the pixel art specific communities. In the past, more artists on Twitter commented on each other’s works, but maybe people have moved on to somewhere else?

I was a little disappointed by micro.blog. It’s possible there were other artists participating in octobit or inktober there, but I didn’t see them. With no hashtags (or even an art-specific emoji tag), it’s impossible to discover people working on drawing challenges like octobit. Still, I’m glad I joined, because it encouraged me to post to my own site.

Conclusion

I had a great time participating in octobit this year, and I look forward to joining again in future years. Next time around, I will comment more on others artwork to feel more connected to the community. I plan to be even better at pixel art next year, too. :-)

Copyright 2018. Released under Creative Commons Attribution License.

www.timswast.com/blog/2018...