Exploring and Analyzing Data in JSON Files on Data Lake Volumes

1. Introduction to Data Lakes and JSON Data

1.1 What is a Data Lake

The data lake is an important component of the Lakehouse, allowing you to store all structured and unstructured data in its original format without needing to define a schema upfront. This fully embodies the advantages of lakehouse unification, extending Lakehouse data management to the unified management of unstructured data, no longer limited to structured data management within the data warehouse. This flexibility makes it an ideal choice for big data analysis, as you can use different analysis methods and tools to process data as needed. A data lake typically consists of the following parts:

• Storage Layer: Such as object storage (S3, OSS, COS, etc.)
• Metadata Management: Used for organizing and discovering data
• Processing Engine: Used for analyzing and transforming data

1.2 Volumes in the Data Lake

In modern data platforms like the Lakehouse, a Volume is an abstraction that represents a specific location in an external storage system (e.g., a path in object storage). Volumes allow the data platform to seamlessly access data stored in external systems without actually copying or moving that data.

Key features of Volumes:

• Direct connection to external storage systems
• Allows in-place querying without ETL
• Supports multiple file formats (JSON, CSV, Parquet, etc.)
• Can integrate with tables, providing SQL access capabilities

1.3 JSON Data Format

JSON (JavaScript Object Notation) is a lightweight data interchange format with the following features:

• Semi-Structured: Flexible key-value pair structure
• Nesting Capability: Supports complex hierarchical structures and arrays
• Broad Support: Almost all programming languages provide JSON parsing support
• Human Readable: Easier to understand compared to binary formats

In the big data domain, JSON is widely used for storing event data, API responses, log files, etc. Its flexibility makes it ideal for storing data with varying shapes.

2. Case Study: GitHub Event Data

2.1 Dataset Overview

GitHub event data is a publicly available dataset recording the time series of all public activities on the GitHub platform. In this case study, we analyze GitHub event data stored in the gh_archive Volume.

Data Source Details:

• Volume Name: gh_archive
• File Path: 2025-05-14-0.json.gz (representing events from the 00:00-01:00 period on May 14, 2025)
• File Format: Compressed JSON file (using gzip compression)
• Data Volume: A single file is approximately 85.6 MB (compressed), containing nearly 200,000 event records

Data Structure: Each record contains the following main fields:

• id: Unique event identifier
• type: Event type (e.g., PushEvent, PullRequestEvent, etc.)
• actor: Information about the user who performed the action
• repo: Information about the related code repository
• org: Information about the related organization (if applicable)
• payload: Detailed event information (varies by event type)
• created_at: Event creation time
• public: Whether the event is public

3. Data Lake Exploration and Analysis via SQL

3.1 Directly Querying JSON Files

The Lakehouse data lake platform allows direct SQL queries on files stored on Volumes, without needing to load them into tables first:

-- Analyze event type distribution
SELECT type, COUNT(*) as count 
FROM VOLUME gh_archive
USING json
OPTIONS('compression'='gzip')
FILES('2025-05-14-0.json.gz')
GROUP BY type
ORDER BY count DESC

Query Result:

type                         count
----------------------------  ------
PushEvent                     131341
CreateEvent                   21700
PullRequestEvent              12537
IssueCommentEvent             7807
WatchEvent                    7196
DeleteEvent                   4832
PullRequestReviewEvent        3571
IssuesEvent                   3569
PullRequestReviewCommentEvent 2362
ForkEvent                     1537
ReleaseEvent                  1261
...

Features:

• Zero ETL: No need to extract, transform, and load data beforehand
• Flexible Access: Direct access to nested fields
• Ad-Hoc Queries: Support for immediate data exploration

3.2 Handling Nested and Complex JSON Structures

JSON data often contains nested objects and arrays. SQL provides direct access to these complex structures:

-- Analyze file operation type distribution in PushEvent commits
SELECT 
  CASE 
    WHEN LOWER(payload.commits[0].message) LIKE '%add%' THEN 'Add'
    WHEN LOWER(payload.commits[0].message) LIKE '%fix%' THEN 'Fix'
    WHEN LOWER(payload.commits[0].message) LIKE '%update%' THEN 'Update'
    WHEN LOWER(payload.commits[0].message) LIKE '%remove%' OR LOWER(payload.commits[0].message) LIKE '%delete%' THEN 'Remove'
    WHEN LOWER(payload.commits[0].message) LIKE '%refactor%' THEN 'Refactor'
    WHEN LOWER(payload.commits[0].message) LIKE '%deploy%' THEN 'Deploy'
    WHEN LOWER(payload.commits[0].message) LIKE '%test%' THEN 'Test'
    WHEN LOWER(payload.commits[0].message) LIKE '%doc%' THEN 'Documentation'
    ELSE 'Other'
  END as commit_type,
  COUNT(*) as count,
  ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (), 2) as percentage
FROM VOLUME gh_archive
USING json
OPTIONS('compression'='gzip')
FILES('2025-05-14-0.json.gz')
WHERE type = 'PushEvent' 
  AND payload.commits IS NOT NULL 
  AND SIZE(payload.commits) > 0
GROUP BY commit_type
ORDER BY count DESC

Query Result:

commit_type    count   percentage
------------   ------  ----------
Other          46901   35.75
Update         42211   32.17
Deploy         17513   13.35
Add            11775   8.97
Fix            5027    3.83
Test           3389    2.58
Remove         3174    2.42
Documentation  637     0.49
Refactor       575     0.44

Features:

• Path Navigation: Use dot notation to access nested objects
• Array Indexing: Use square brackets to access array elements
• Array Functions: Use functions like SIZE() to handle arrays
• Conditional Analysis: Use CASE statements for classification and pattern recognition

3.3 Using JSON Paths to Access Deeply Nested Data

Path expressions can be used to deeply access multi-level nested structures in JSON documents:

-- Analyze branch information in PR events
SELECT 
  payload.pull_request.base.ref as base_branch,
  payload.pull_request.head.ref as head_branch,
  COUNT(*) as count
FROM VOLUME gh_archive
USING json
OPTIONS('compression'='gzip')
FILES('2025-05-14-0.json.gz')
WHERE type = 'PullRequestEvent'
  AND payload.action = 'opened'
  AND payload.pull_request.base.ref IS NOT NULL
  AND payload.pull_request.head.ref IS NOT NULL
GROUP BY base_branch, head_branch
ORDER BY count DESC
LIMIT 15

Query Result:

base_branch   head_branch                                               count
------------  --------------------------------------------------------  -----
main          main                                                      508
master        master                                                    499
main          dependabot/bundler/kamal-2.6.0                            93
main          develop                                                   72
main          dev                                                       63
main          github-actions/upgrade-dev-deps-main                      35
dev           dev                                                       27
main          github-actions/upgrade-main                               26
main          prBranch                                                  25
develop       develop                                                   20
main          test                                                      20
...

3.4 Complex Conditional Filtering and Regular Expressions

Use regular expressions and complex condition combinations to filter JSON data:

-- Find PRs created by bot accounts and analyze target repositories
SELECT 
  REGEXP_EXTRACT(actor.login, '(.*?)\\[bot\\]') as bot_name,
  repo.name as target_repo,
  COUNT(*) as pr_count
FROM VOLUME gh_archive
USING json
OPTIONS('compression'='gzip')
FILES('2025-05-14-0.json.gz')
WHERE 
  type = 'PullRequestEvent' 
  AND payload.action = 'opened'
  AND actor.login LIKE '%[bot]'
GROUP BY bot_name, target_repo
HAVING pr_count > 5
ORDER BY pr_count DESC
LIMIT 15

Query Result:

bot_name        target_repo                                       pr_count
--------------  ------------------------------------------------  --------
dependabot      GolfredoPerezFernandez/nodo-blockchain-blockscout  12
renovate        gAmUssA/flink-java-flights                         12
github-actions  hofferkristof/laravel-lang                         12
dependabot      j4v3l/tapo-exporter                                11
dependabot      Sudhanshu-Ambastha/GPT-3-webapp-in-reactjs         10
dependabot      jamespurnama1/new-portfolio                        10
github-actions  zzllbj/lang                                        9
...

3.5 Advanced Aggregation and Conditional Statistics

Use conditional aggregation to simultaneously analyze multiple metrics:

-- Analyze repository activity: Calculate the number of different event types per repository
SELECT
  repo.name,
  COUNT(DISTINCT actor.id) as unique_contributors,
  SUM(CASE WHEN type = 'PushEvent' THEN 1 ELSE 0 END) as push_count,
  SUM(CASE WHEN type = 'PullRequestEvent' THEN 1 ELSE 0 END) as pr_count,
  SUM(CASE WHEN type = 'IssueEvent' THEN 1 ELSE 0 END) as issue_count,
  SUM(CASE WHEN type = 'WatchEvent' THEN 1 ELSE 0 END) as watch_count,
  SUM(CASE WHEN type = 'ForkEvent' THEN 1 ELSE 0 END) as fork_count,
  COUNT(*) as total_events
FROM VOLUME gh_archive
USING json
OPTIONS('compression'='gzip')
FILES('2025-05-14-0.json.gz')
GROUP BY repo.name
HAVING total_events > 100
ORDER BY total_events DESC
LIMIT 10

Query Result:

repo.name                    unique_contributors  push_count  pr_count  issue_count  watch_count  fork_count  total_events
--------------------------   -------------------  ----------  --------  -----------  -----------  ----------  ------------
samgrover/mb-archive         1                    1289        0         0            0            0           1289
chrisxero/bitdepth-microblog 1                    1087        0         0            0            0           1087
iniadittt/iniadittt          1                    877         0         0            0            0           877
0xios/news-momentum-1        1                    730         0         0            0            0           730
JamyJones/Pastebin           1                    636         0         0            0            0           636
SoliSpirit/proxy-list        1                    586         0         0            0            0           586
...

3.6 Window Function Analysis

Use window functions for more complex ranking and grouping analysis:

-- Window function analysis: Calculate the most active users within each organization
SELECT
  org_login,
  username,
  event_count,
  rank_in_org
FROM (
  SELECT
    org.login as org_login,
    actor.login as username,
    COUNT(*) as event_count,
    RANK() OVER (PARTITION BY org.login ORDER BY COUNT(*) DESC) as rank_in_org
  FROM VOLUME gh_archive
  USING json
  OPTIONS('compression'='gzip')
  FILES('2025-05-14-0.json.gz')
  WHERE org.login IS NOT NULL
  GROUP BY org_login, username
) t
WHERE rank_in_org <= 3 AND org_login IN (
  SELECT org.login
  FROM VOLUME gh_archive
  USING json
  OPTIONS('compression'='gzip')
  FILES('2025-05-14-0.json.gz')
  WHERE org.login IS NOT NULL
  GROUP BY org.login
  ORDER BY COUNT(*) DESC
  LIMIT 5
)
ORDER BY org_login, rank_in_org

Query Result:

org_login                  username                            event_count  rank_in_org
-------------------------  ----------------------------------  -----------  -----------
blueprint-house            ChineeWetto                         559          1
curseforge-mirror          github-actions[bot]                 525          1
flyteorg                   github-actions[bot]                 444          1
flyteorg                   flyte-bot                           1            2
microsoft                  wingetbot                           67           1
microsoft                  microsoft-github-policy-service[bot] 57          2
microsoft                  jstarks                             29           3
static-web-apps-testing-org swa-runner-app[bot]                2178         1
static-web-apps-testing-org mkarmark                           164          2
static-web-apps-testing-org github-actions[bot]                28           3

3.7 Text Analysis and Time Processing

Use string functions and time functions to analyze time patterns in JSON data:

-- Verify the relationship between event type and hourly activity
SELECT 
  SUBSTR(created_at, 12, 2) as hour_of_day,
  type,
  COUNT(*) as event_count
FROM VOLUME gh_archive
USING json
OPTIONS('compression'='gzip')
FILES('2025-05-14-0.json.gz')
GROUP BY hour_of_day, type
ORDER BY hour_of_day, event_count DESC

Query Result:

hour_of_day  type                         event_count
-----------  ---------------------------  -----------
00           PushEvent                    131341
00           CreateEvent                  21700
00           PullRequestEvent             12537
00           IssueCommentEvent            7807
00           WatchEvent                   7196
...

3.8 Analyzing PR Merge Patterns

Analyze Pull Request merge trends:

-- Analyze PR merge trends: View PRs from creation to merging
SELECT 
  SUBSTRING(payload.pull_request.merged_at, 1, 10) as merge_date,
  COUNT(*) as merged_prs
FROM VOLUME gh_archive
USING json
OPTIONS('compression'='gzip')
FILES('2025-05-14-0.json.gz')
WHERE 
  type = 'PullRequestEvent' 
  AND payload.action = 'closed' 
  AND payload.pull_request.merged = true
  AND payload.pull_request.merged_at IS NOT NULL
GROUP BY merge_date
ORDER BY merge_date

Query Result:

merge_date  merged_prs
----------  ----------
2025-05-13  3
2025-05-14  4448

3.9 Loading File Data into Tables for Further Analysis

For data that needs repeated analysis, creating tables can improve query performance:

CREATE TABLE github_events AS
SELECT 
  id,
  type,
  actor.login as actor_name,
  actor.id as actor_id,
  repo.name as repo_name,
  repo.id as repo_id,
  org.login as org_name,
  created_at,
  public,
  payload
FROM VOLUME gh_archive
USING json
OPTIONS('compression'='gzip')
FILES('2025-05-14-0.json.gz')

Advantages:

• Improved Performance: Tables can use indexes and caching to speed up queries
• Data Transformation: Transformations and column renaming can be applied during loading
• Persistent Access: Create persistent views to avoid repeated parsing of raw data

4. Key Technical Features of Data Lake Analysis

4.1 Schema Flexibility

A core advantage of data lake analysis is schema flexibility:

• Schema-on-Read: Data structure only needs to be defined at query time
• Partial Field Access: Only query needed fields, ignoring others
• Evolution Adaptation: As data structures change, queries can easily adapt

For example, in our GitHub event analysis, we can focus only on specific event types or specific fields without processing the entire data structure.

4.2 Computation Pushdown and Filter Optimization

The Lakehouse data lake technology supports computation pushdown, pushing filter and transformation operations to the data source:

-- Example of an efficient filter query
SELECT actor.login, COUNT(*) as event_count
FROM VOLUME gh_archive
USING json
OPTIONS('compression'='gzip')
FILES('2025-05-14-0.json.gz')
WHERE type = 'PushEvent'
GROUP BY actor.login
ORDER BY event_count DESC
LIMIT 10

4.3 Unified Data Access and Format Selection

Apply SQL analysis to different types of data files in the same Volume:

-- 1. Query JSON files
SELECT COUNT(*) FROM VOLUME gh_archive
USING json
OPTIONS('compression'='gzip')
FILES('2025-05-14-0.json.gz');

-- 2. If the Volume has CSV files, similar syntax can be used for querying
-- SELECT * FROM VOLUME volume_name
-- USING csv
-- OPTIONS('header'='true')
-- FILES('data.csv');

4.4 Distributed Processing Capabilities

Data lake analysis engines are typically based on distributed computing frameworks, capable of processing large-scale datasets:

• Parallel Processing: Automatically decomposes queries into parallel execution tasks
• Memory Management: Optimizes memory usage, processing data exceeding single-machine memory capacity
• Fault Tolerance: Handles node failures and recovery

In GitHub event analysis, even though individual files are relatively small, the same queries can scale to analyze TB-level historical event data.

5. Data Analysis Process and Methodology

Through the GitHub event data case study, we can summarize a methodology for analyzing JSON data in a data lake:

5.1 Exploratory Data Analysis Process

1. Initial Preview: Get data samples to understand the overall structure

   SELECT * FROM VOLUME gh_archive
   USING json
   OPTIONS('compression'='gzip')
   FILES('2025-05-14-0.json.gz')
   LIMIT 10
   

2. Overview Statistics: Understand data distribution and main dimensions

   SELECT type, COUNT(*) as count 
   FROM VOLUME gh_archive
   USING json
   OPTIONS('compression'='gzip')
   FILES('2025-05-14-0.json.gz')
   GROUP BY type
   ORDER BY count DESC
   

3. Deep Analysis: Refined analysis targeting specific domains

   SELECT 
     actor.login as username, 
     COUNT(*) as event_count
   FROM VOLUME gh_archive
   USING json
   OPTIONS('compression'='gzip')
   FILES('2025-05-14-0.json.gz')
   GROUP BY username
   ORDER BY event_count DESC
   LIMIT 15
   

4. Correlation Analysis: Cross-analysis connecting multiple dimensions

   SELECT 
     org.login as organization,
     type,
     COUNT(*) as event_count
   FROM VOLUME gh_archive
   USING json
   OPTIONS('compression'='gzip')
   FILES('2025-05-14-0.json.gz')
   WHERE org.login IS NOT NULL
   GROUP BY organization, type
   ORDER BY organization, event_count DESC
   LIMIT 20
   

5.2 Data Insight Methods

In the GitHub event analysis, we discovered several key insights:

1. Activity Distribution: Understanding the proportion of different event types, discovering that Push events dominate (66%)
2. Automation Trends: Through user activity analysis, discovering that automated bot accounts (such as github-actions[bot]) contribute a large amount of activity
3. Organizational Ecosystem: Identifying the most active organizations, understanding the composition of the GitHub ecosystem
4. Development Behavior: Through commit message analysis, understanding developers' work patterns and focus areas
5. Branch Usage Patterns: main and master are still the most common target branch names
6. Bot Activity: dependabot, renovate, and github-actions are the most active automation bots

These insights come directly from SQL queries, demonstrating the powerful capability of SQL as a data analysis tool.

6. Best Practices and Optimization Tips

6.1 JSON Data Analysis Optimization

• Selective Field Reading: Only select the fields needed for analysis

  -- Only read necessary fields instead of all fields
  SELECT 
    type, 
    actor.login, 
    repo.name
  FROM VOLUME gh_archive
  USING json
  OPTIONS('compression'='gzip')
  FILES('2025-05-14-0.json.gz')
  LIMIT 10
  

• Predicate Pushdown: Apply filter conditions as early as possible in the query

• Predicate Pushdown: Filter conditions (WHERE type = 'PushEvent') are pushed down to the storage layer, leveraging columnar statistics (such as max/min/bloom filters) to skip data blocks that clearly don't match.

• Column Pruning: Only read relevant columns (actor.login and type), skipping parsing of unrelated fields.

• Filter Optimization: After the storage layer loads data in blocks, further filter out non-matching records in memory, ultimately retaining only valid data for computation.

  -- Filter data early to reduce processing volume
  SELECT COUNT(*) 
  FROM VOLUME gh_archive
  USING json
  OPTIONS('compression'='gzip')
  FILES('2025-05-14-0.json.gz')
  WHERE type = 'PushEvent'
  

• Properly Handle NULL Values: Use IS NULL and IS NOT NULL to check for missing fields

  -- Identify and handle missing data
  SELECT 
    type,
    COUNT(*) as total_count,
    SUM(CASE WHEN org.login IS NULL THEN 1 ELSE 0 END) as without_org,
    SUM(CASE WHEN org.login IS NOT NULL THEN 1 ELSE 0 END) as with_org
  FROM VOLUME gh_archive
  USING json
  OPTIONS('compression'='gzip')
  FILES('2025-05-14-0.json.gz')
  GROUP BY type
  ORDER BY total_count DESC
  

6.2 Data Lake Query Best Practices

1. Avoid Full Table Scans: Use filter conditions whenever possible
2. Use Aggregation Wisely: When data volume is large, prioritize distributed aggregation
3. Selective Projection: Only select necessary columns to reduce I/O and memory usage
4. Appropriate Data Transformation: For frequently queried data, consider converting to more efficient formats
5. Leverage Data Partitioning: Logical partitioning based on file names or paths
6. Pay Attention to Error Handling: Handle JSON parsing errors and type conversion issues

7. Summary

Key features of data lake exploration and analysis through SQL:

1. No ETL Needed: Directly query raw data, reducing data preparation time
2. Flexibility: Adapts to semi-structured data without predefined schemas
3. Ease of Use: Uses familiar SQL syntax, lowering the learning curve
4. Scalability: Handles data from GB to PB levels
5. Unified Access: The same interface handles multiple data formats
6. Exploration-Friendly: Supports iterative data exploration processes
7. Performance Optimization: Provides various optimization techniques and strategies

SQL analysis on the data lake combines the ease of use of traditional SQL with the flexibility and scalability of big data processing, making it a powerful tool for modern data analysis. Through the GitHub event data analysis case study in this article, we demonstrated how to use SQL to directly query and analyze JSON files stored on data lake Volumes, quickly obtaining valuable data insights.

References

JSON Data Type

JSON Functions External Volume