Structured, Semi-Structured and Unstructured Data

Structured data is data organized into fixed fields with known names and types, such as tables with rows and columns. Each record holds the same kind of attributes, for example an id, a title, a rating, and an available_date, so values can be filtered, joined, and aggregated reliably. Because the layout is predictable, structured data fits a fixed schema and SQL-compatible querying in Cloud SQL (opens in a new tab) and BigQuery (opens in a new tab).

1.1Fixed Schema and Predictable Imports

Fixed schema means structured data defines field names, data types, and required values before the data is stored and queried. In BigQuery, a table schema can be auto-detected on import or supplied as a JSON object through the API, and editing the auto-detected schema to map key properties such as titles is recommended. For structured imports with metadata, the schema uses a REQUIRED STRING id field to identify each document and a NULLABLE STRING jsonData field to carry the record contents, while records without supplied metadata get an automatically generated id.

Structured records stored in Cloud Storage (opens in a new tab) must use JSON Lines or NDJSON format, where each line is one document made of a set of fields. Each file must be less than 2 GB, each row must be less than 1 MB, and up to 1,000 files can be imported in a single request. Tables that use external data sources cannot be imported, and columns with flexible column names are skipped on import, which shows why consistent field definitions matter.

1.2SQL Compatibility and Relational Mapping

SQL compatibility means structured data can be stored in tables and queried by column values, primary keys, and relational operations. Cloud SQL provides a relational database with full SQL support, where a table holds rows, each row holds columns, and a primary key gives each row a unique ID. BigQuery provides interactive querying for large tabular datasets, including public datasets that follow the same table-and-column pattern.

Firestore in Datastore mode (Datastore) (opens in a new tab) shows the same structured idea in NoSQL form with a SQL-like query language. Its concepts map directly to relational concepts:

• Kind maps to table in a relational database
• Entity maps to row in a relational database
• Property maps to column in a relational database
• Key maps to primary key in a relational database

Unlike a relational database, Datastore is schemaless and does not require every entity of the same kind to have the same properties. Its queries scale with the size of the result set rather than the size of the dataset, but Datastore does not support joins, inequality filtering on multiple properties, or filtering based on subquery results.

1.3Choosing Between Cloud SQL and BigQuery

Choosing between Cloud SQL and BigQuery means matching the workload to transaction processing or analytical processing. Cloud SQL is the choice for structured transactional workloads that need strict consistency and full relational behavior. Data flows from the application to the Cloud SQL instance in the selected region, where rows are written with ACID guarantees and read back with SQL.

BigQuery is the choice for structured analytical workloads that need to scan and aggregate large tables quickly. Data flows from BigQuery datasets into analysis and search processes, where the schema defines columns and types for interactive querying. The decision depends on workload type and query pattern:

Evaluating data characteristics and storage solutions means classifying a use case as structured, semi-structured, or unstructured and then choosing the Google Cloud service that fits that shape. Structured data has a fixed schema and fits tables, unstructured data has no fixed schema and fits files and objects, and semi-structured data has a flexible or nested shape and fits document and wide-column stores. In Google Cloud, BigQuery (opens in a new tab) serves structured analytics, Cloud Storage (opens in a new tab) serves unstructured objects, and Firestore in Datastore mode and Bigtable serve flexible large-scale workloads.

2.1Structured Data Shapes and Import Rules

Structured data is data with a fixed, predefined schema of rows and columns where each field has a known type. BigQuery imports structured data from BigQuery datasets, from Cloud Storage files in JSON Lines or NDJSON format, and from local JSON objects uploaded through the API. The schema can be auto-detected or supplied as a JSON object through the API.

Structured data with metadata uses an id field to identify each document and a jsonData field that contains the data. If no id is supplied, an id is generated for the record. Structured files in Cloud Storage must use JSON Lines or NDJSON, with each file less than 2 GB, each row less than 1 MB, and up to 1,000 files in a single import request. A hotel record with title, location.address, available_date, rating, and a room_types array is an example of structured data that still allows nested objects and lists inside a record.

2.2Unstructured Data and Object Storage

Unstructured data is data without a fixed table schema, such as documents, images, video, backups, and web content. Cloud Storage stores unstructured data as objects in a flat hierarchy of buckets, where each object has a globally unique ID plus system-assigned and user-defined metadata. Applications access objects by ID using REST APIs or client libraries, and data can be accessed from inside and outside Google Cloud.

Searchable unstructured formats include TXT, PDF, HTML, DOCX, PPTX, XLSX, XLSM, and text-based formats such as XML and XHTML. Each file must be less than 200 MB for default import, and up to 100,000 files can be imported at a time. Data import from a bucket or folder is recursive, so files in subfolders are imported when the parent bucket or folder is specified. When metadata is needed, the unstructured files stay in Cloud Storage while a BigQuery table or NDJSON file holds the metadata, and the uri field in each metadata record points to the Cloud Storage location plus its mimeType, such as application/pdf.

Unstructured import with metadata follows an ordered flow:

1. Create metadata: Create a BigQuery table or NDJSON file with id, jsonData or structData, and content fields.
2. Store documents: Put the unstructured documents into a Cloud Storage bucket, which can be the same bucket as the metadata or a different bucket.
3. Link by URI: Data flows from the metadata record to the document because the uri field in each record points to the Cloud Storage location of that document.

2.3Semi-Structured Data and NoSQL Options

Semi-structured data is data that has organization such as keys, tags, or nesting but does not enforce a rigid table schema across all records. Firestore in Datastore mode (Datastore) (opens in a new tab) is a NoSQL document database built for automatic scaling, high performance, and ease of application development. Unlike rows in a relational table, entities of the same kind can have different properties, and different entities can use the same property name with different value types.

Datastore organizes data as kinds containing entities, where a kind is like a table, an entity is like a row, a property is like a column, and a key is like a primary key. Datastore provides atomic transactions where all operations succeed or none occur, high availability through redundancy, strong consistency for queries, encryption at rest, and fully managed operation with no planned downtime. Reads scale because query performance scales with the size of the result set rather than the size of the dataset, so a query returning 100 entities performs the same whether it searches hundreds or millions of entities. For workloads that do not require ACID transactions or whose data is not highly structured, Bigtable is the alternative wide-column option.

2.4Matching Characteristics to Storage Solutions

Matching data characteristics to storage solutions means comparing schema rigidity, query pattern, and scale, then selecting the service whose design fits those needs. Structured transactional data flows into relational stores, structured analytical data flows into BigQuery datasets and tables, flexible operational data flows into Datastore or Bigtable, and files and media flow into Cloud Storage buckets and objects.

Unstructured and semi-structured data needs describe workloads where data does not fit neatly into fixed rows and columns. Cloud Storage (opens in a new tab) holds unstructured objects such as documents, media, and archives, while NoSQL services hold semi-structured records with flexible fields and nested values. Classifying a use case by how fixed its shape is determines whether an object store, a document database, or a wide-column store is the suitable choice.

3.1What Counts as Unstructured Data

Unstructured data is content without a fixed schema, where each file is stored and read as a whole object rather than as queryable rows. Common examples include multimedia and archives such as PDFs, scanned images, videos, large images, movies, website assets, backups, and regulatory archives. Typical searchable formats include:

• TXT, PDF, and HTML for documents and web pages
• DOCX, PPTX, XLSX, and XLSM for office productivity files
• JSON, Markdown, XHTML, and XML when stored as text files for search
• Large immutable blobs such as backups, long-tail multimedia, and archives

Cloud Storage is the object storage service for unstructured data, and it stores data as objects inside a flat hierarchy of buckets. Each object has a globally unique ID plus system-assigned and user-defined metadata, and applications access objects by ID using REST APIs or client libraries from inside or outside Google Cloud. Data flows from a source bucket or folder into processing, and import is recursive, so files inside subfolders are included when a bucket or folder is selected. Limits for search ingestion include less than 200 MB per file and up to 100,000 files at a time, which makes Cloud Storage suitable for large collections of documents, static website content, and directly downloaded objects.

3.2What Counts as Semi-Structured Data

Semi-structured data has an organizing structure such as fields, keys, and nested objects, but it does not require every record to share the same schema. Examples include NoSQL documents with nested fields like hotel records with titles, locations, ratings, and room-type arrays, plus user profiles, product catalogs, and wide-column records where columns can vary by row. Structured data prepared for import often uses JSON Lines or NDJSON, where each line is one record, each file must be less than 2 GB, each row must be less than 1 MB, and up to 1,000 files can be imported in one request.

Firestore in Datastore mode (Datastore) (opens in a new tab) is the NoSQL document database for semi-structured application data that needs automatic scaling, high availability, and strong consistency. Datastore organizes data as kinds containing entities, where each entity has a key and a flexible set of properties, so entities of the same kind can have different properties or different value types for the same property name. Datastore supports atomic transactions where either all operations succeed or none occur, and queries scale with the size of the result set because queries are served by prebuilt indexes. Bigtable (opens in a new tab) is the wide-column NoSQL option for data that is not highly structured and does not require ACID transactions, especially at very large scale with high read and write throughput.

3.3Matching Data Needs to Google Cloud Storage Solutions

Matching data needs to Google Cloud storage solutions means choosing the service whose data model and access pattern fit the shape and use of the data. Unstructured objects that are written once and read as files belong in object storage, while semi-structured records that are frequently read, written, and queried by key or property belong in a NoSQL database. When unstructured documents need descriptive fields, a metadata record uses a uri field that points from the metadata store to the object location in the Cloud Storage bucket, so the file stays in object storage while its title and attributes stay queryable.

Access frequency further refines the Cloud Storage choice because each storage class trades retrieval cost for retention length. Frequently accessed data such as data lakes, websites, streaming video, and mobile content uses Standard, while predictable infrequent data uses Nearline for at least 30 days, Coldline for at least 90 days, and Archive for at least 365 days. When access patterns vary or retention is unpredictable, Autoclass automatically transitions each object between classes based on its access pattern.

Key Facts

• Structured data uses a fixed schema with known field names and types and is queried with SQL in Cloud SQL for OLTP and BigQuery for OLAP.
• Cloud Storage stores unstructured data as objects in buckets accessed by globally unique object ID, with limits of less than 200 MB per file and up to 100,000 files per search import.
• Firestore in Datastore mode stores semi-structured documents where entities of the same kind can have different properties, while Bigtable stores wide-column semi-structured data without ACID transaction support.
• A metadata record links to an unstructured object when its uri field points to the Cloud Storage location of the document.
• Cloud Storage classes include Standard for frequent access, Nearline for at least 30 days, Coldline for at least 90 days, Archive for at least 365 days, and Autoclass for automatic transitions.