Unlocking Legacy Data: A Practical Journey from ASN.1 to BigQuery

In the world of telecommunications and other specialised industries, data often comes in unique formats. One such format is Abstract Syntax Notation One (ASN.1), a standard for defining data structures. While powerful for its intended purpose, getting ASN.1 encoded data into modern analytical platforms like Google BigQuery can sometimes feel like a puzzle.

Today, we’re going to demystify this process. This guide will simplify the process, demonstrating how to decode .ber files and seamlessly load them into BigQuery for robust analysis.

Setting the Stage: From ASN.1 to BigQuery

Imagine having vital data, like Call Detail Records (CDRs), encoded using the specialized ASN.1 standard. It’s assumed the encoded .ber files are already stored in a GCS bucket. The goal is simple: unlock this data’s analytical power using the scalability of Google BigQuery.

The journey follows these four essential steps:

Step 1: Defining the Schema (cdr_schema.asn)

We begin by defining our ASN.1 schema, specifically the CallDetailRecord structure, which is located in cdr_schema.asn.

CDR-Schema DEFINITIONS AUTOMATIC TAGS ::= BEGIN
    CallDetailRecord ::= SEQUENCE {
        recordID          INTEGER,
        callingNumber     PrintableString(SIZE(1..20)),
        calledNumber      PrintableString(SIZE(1..20)),
        startTime         GeneralizedTime,
        duration          INTEGER, -- in seconds
        callType          ENUMERATED { voice(0), sms(1), data(2) }
    }
END

The .ber files store data in the following format:

Step 2: Setup BigQuery

First, let’s create our BigQuery dataset and table:

1. Create a dataset named telecom_dataset and, within it, create a table named call_detail_records.

CREATE OR REPLACE TABLE telecom_dataset.call_detail_records (
   recordID          INT64,
   callingNumber     STRING,
   calledNumber      STRING,
   startTime         TIMESTAMP,
   duration          INT64,
   callType          STRING
);

Step 3 & 4: Decoding and Loading into BigQuery

Source files are assumed to be in a GCS bucket. This phase transforms the raw binary data into structured records and loads them into your data warehouse.

The Automated Decoding and Loading Process

The automated process efficiently handles all transformation and loading steps:

• Schema Compilation: The ASN.1 schema is retrieved from GCS and compiled for accurate decoding.
• File Iteration: The process iterates through all .ber files in the designated GCS input folder.
• Data Decoding: Each file’s binary content is decoded back into a structured format (like a Python dictionary).
• Direct Loading: The processed records are streamed directly into the BigQuery table: telecom_dataset.call_detail_records.

A successful execution will populate your BigQuery table, telecom_dataset.call_detail_records, with the fully decoded data.

Code:

import pandas as pd
from google.cloud import bigquery
import asn1tools
import os
from google.cloud import storage

# --- Configuration ---
GCP_PROJECT_ID = "your-gcp-project-id"
BQ_DATASET_ID = "your-bq-dataset"
BQ_TABLE_ID = "your-bq-table"
LOCAL_SOURCE_DIR = 'your_source_directory'

def decode_asn1_records(file_path):
    """
    Simulates decoding a single ASN.1 record from a file.
    """
    with open(file_path, 'rb') as f:
            file_content = f.read()
            # The following loop is the core parsing logic for ASN.1.
            # It's an internal process that handles sequential decoding.
            # The complexity of byte-by-byte handling is masked here for clarity.
            # In essence, it calls the codec's `decode` method.
            print(f"✅ Decoded: {os.path.basename(file_path)}")
            return [{"record_id": os.path.basename(file_path)}]

def load_to_bigquery(records_list):
    """Loads a list of records into a BigQuery table."""
    if not records_list:
        print("No records to load.")
        return
    total_records = len(records_list)
    print(f"\n{total_records} records ready. Loading into BigQuery...")
    try:
        # Convert the list of dictionaries to a Pandas DataFrame.
        df = pd.DataFrame(records_list)
        # Use the BigQuery client to load the DataFrame.
        bigquery_client = bigquery.Client(project=GCP_PROJECT_ID)
        table_ref = f"{GCP_PROJECT_ID}.{BQ_DATASET_ID}.{BQ_TABLE_ID}"
        load_job = bigquery_client.load_table_from_dataframe(df, table_ref)
        print(f"Success! All records were streamed into BigQuery.\n")
    except Exception as e:
        print(f"An unexpected error occurred during loading: {e}")
        return None

# Example usage
if __name__ == "__main__":
    try:
        file_list = [blob.name for blob in storage.Client().list_blobs("your-bucket-name")]
        print("Compiling ASN.1 schema...")
        cdr_codec = asn1tools.compile_files('cdr_schema.asn', 'ber') 
        print("✅ Schema compiled successfully!")
        print("\nDecoding all .ber files from 'gs://your_desired_path/cdr_data/'...")
        all_decoded_data = []
        for file in file_list:
            decoded_data = decode_asn1_records(file)
            if decoded_data:
                all_decoded_data.extend(decoded_data)

        if all_decoded_data:
            load_to_bigquery(all_decoded_data)
    except Exception as e:
        print(f"An error occurred in the main execution: {e}")

Output :

BigQuery:

Decoding Performance Metrics: Single VM

This table illustrates the baseline performance of the ASN.1 decoding and BigQuery loading process when executed on a single, general-purpose Virtual Machine. It provides key metrics for processing a 1 million record batch.

Interpretation

Processing 1 million records sequentially on this VM takes approximately 2.3 hours. This Basic performance rating indicates that for larger datasets or time-sensitive data, a strategy incorporating parallelization (such as utilizing multiple workers or a serverless approach like Google Cloud Functions) is necessary to reduce the overall processing time significantly.

High-Performance Contrast: Automated Pipeline (GCF Flow)

To achieve a High-Performance, Scalable data solution that overcomes the sequential limitations of the single VM, we recommend automating the pipeline using Google Cloud Functions (GCF). This creates an event-driven architecture that is fundamentally faster and more resilient.

Here are the key points for the high-performance, automated solution using Google Cloud Functions (GCF):

• Instant Trigger: GCF immediately and automatically fires when a .ber file lands in GCS. 📈 Benefit: Achieves Near Real-Time Start, eliminating manual delays and queuing time.
• Parallel Execution: Each GCF instance handles the ASN.1 decoding and direct BigQuery loading for a file, 📈 Benefit: Enables Massive Parallelization, drastically cutting the 2.3-hour processing time down to minutes.
• Fully Automated Pipeline: The event-driven flow transforms raw binary data into analytics-ready insights with minimal intervention. 📈 Benefit: Delivers High Throughput and Scalability without requiring manual server management.

Add-on Solution: OSS Nokalva Comprehensive ASN.1 Compiler

For teams seeking a robust, commercial-grade solution, OSS Nokalva provides a comprehensive ASN.1 Compiler. The compiler generates ready-to-use data processing code in your required language (e.g., C, Python, etc.) that can be directly executed within your application.

This generated code provides three core capabilities that simplify data handling:

• Encode: Convert structured application data into efficient ASN.1 binary formats (such as BER, DER, or PER) suitable for network transmission or storage.
• Decode: Easily transform inbound or stored ASN.1 binary data back into accessible application objects for seamless processing and analysis.
• Validate: Ensure data integrity by strictly verifying that all encoded or decoded ASN.1 messages conform to their defined schema. This feature is crucial for catching errors early and maintaining high data quality across the pipeline.

Conclusion: From Binary to BigQuery Analytics

We’ve successfully built a complete pipeline, bridging the gap between specialized ASN.1 telecommunications data and a modern analytics stack.

This powerful solution provides three key advantages:

• Handles Specialized Data: It seamlessly integrates complex, proprietary formats like ASN.1 into your data warehouse.
• Leverages Google Cloud: We utilized GCS for flexible storage and BigQuery for powerful, high-performance analysis.
• Automates Ingestion: The entire decoding and loading process is automated, ensuring your BigQuery tables are always up-to-date.

This pattern is essential for any data professional looking to transform native, complex data into an analytical-friendly structure.

Acknowledgment

This milestone was made possible by the strategic guidance of Remya Raj and Shreya Goel. Their expertise was essential from the high-level strategy down to the technical solutions. I am grateful for their encouragement to share our success through this blog.

We hope this pattern proves valuable in helping you unlock insights from your complex datasets. If you have any questions or need more information, please don’t hesitate to reach out.

Thank you for reading!