redshift pt2

Federated Queries in Amazon Redshift

• Enables running SQL queries that span across a Redshift data warehouse and external data sources like Amazon RDS, Amazon Aurora, and PostgreSQL-compatible databases.
• Integrates and queries data stored outside Redshift without needing to load it into the Redshift cluster.
• External data is queried with Redshift Spectrum.
• Extends Redshift Spectrum by enabling access to databases like RDS or Aurora.
• Redshift creates external schemas and tables to define connections to external data sources.
• External tables act as pointers to data in the external systems.
• Runs single query to join data from Redshift with external data from systems such as Amazon RDS, Aurora, or PostgreSQL.
• Executes federated queries using standard SQL.
• Eliminates the need to move or replicate data from external systems to Redshift.
• Optimizes queries involving federated data by pushing operations like filtering, aggregation, and sorting to the external system.
• Integrates data from Redshift with operational or transactional data from external systems like RDS or Aurora.
• Queries and analyzes real-time or operational data in external databases without replicating it into Redshift.
• Supports hybrid environments where some data stays in external systems and other data resides in Redshift.
• Configuration involves setting up Redshift Spectrum to interact with external data sources.
• An external schema is created to define a connection to the external database using the CREATE EXTERNAL SCHEMA command.
• External tables that point to the external data are defined using the CREATE EXTERNAL TABLE statement.
• Standard SQL queries join Redshift data with data from the external system.
• Does not require replicating external data into Redshift, reducing storage and data movement costs.
• Queries external data in place without data synchronization.
• Leverages Redshift's scalable compute power while accessing external data.
• Enables seamless integration and querying of external data sources.

Materialized Views in Amazon Redshift

• Precomputed views store the results of a query physically in the database.
• The results are stored and can be refreshed periodically, as opposed to regular views that recalculate each access.
• Improves query performance, particularly for complex or time-consuming operations.
• The precomputed query result is stored at the time the materialized view is created or refreshed.
• Retrieves the precomputed data upon query, speeding up query performance.
• Can be manually or automatically refreshed to keep the data up to date with changes in underlying tables.
• Provides performance benefits for frequently run queries involving complex calculations or large datasets, reducing the computational load.
• Takes up disk space because they store the actual results.
• The stored data can be compressed, reducing storage requirements.
• Refreshes materialized views manually with the REFRESH MATERIALIZED VIEW command.
• Redshift may perform incremental updates to materialized views if the underlying data has changed, improving efficiency.
• Ideal for storing aggregated results, such as summing sales data by region.
• Data in materialized view is refreshed via REFRESH MATERIALIZED VIEW sales_by_region;
• Speeds up the querying process, especially for complex and computationally expensive queries.
• Reduces the load on the source tables during query execution due to precomputed results.
• Suited for reports or dashboards where the data doesn’t change frequently and fast access is required.
• Requires storage, as the data is physically stored in the database.
• Data might be slightly out of date if not refreshed regularly.
• Direct modifications (inserts/updates/deletes) are not supported; they can only be queried and refreshed.
• Provides a way to precompute and store complex query results, improving performance and reducing query execution time.

Amazon Redshift Spectrum

• Allows running SQL queries directly on data stored in Amazon S3 without needing to load it into the Redshift cluster.
• Enables analytics on exabytes of data stored in Amazon S3.
• Queries both Redshift data (stored in Redshift tables) and data in S3 (using external tables) in a single query.
• Joins data from Redshift tables with external data stored in S3 using SQL queries.
• Accesses data in S3 through external tables defined in Redshift.
• Act as metadata references to S3 data.
• Must be part of an external schema.
• Supports text (CSV, TSV), columnar (Parquet, ORC, Avro), and JSON data formats.
• Optimizes large datasets using JSON and Apache Parquet.
• Data in Amazon S3 can be structured in directories and files (e.g., s3://mybucket/data/).
• Integrates with Redshift clusters, and the cluster needs to have access to the S3 data.
• Runs queries that reference both Redshift and S3 data in parallel.
• Created in Redshift to reference the data in S3.
• Linked to an AWS Glue catalog or Hive Metastore which stores the metadata of the external tables.
• Columnar formats (e.g., Parquet) provide better performance compared to text formats (e.g., CSV, TSV).
• Is optimized for querying large volumes of data.
• Partitioning data in S3 based on columns used in queries (e.g., date, region) helps reduce the amount of data scanned during the query.
• Begins when an external schema points to the data catalog (e.g., AWS Glue or Hive Metastore) is created, via:

    CREATE EXTERNAL SCHEMA spectrum_schema
    FROM DATA CATALOG 
    DATABASE 'external_database'
    IAM_ROLE 'arn:aws:iam::your-aws-account-id:role/your-role'
    CREATE EXTERNAL DATABASE IF NOT EXISTS;

• Queries external data (via SQL), for example:

    CREATE EXTERNAL TABLE spectrum_schema.sales (
        order_id INT,
        order_date DATE,
        total_amount DECIMAL(10, 2)
    )
    STORED AS PARQUET
    LOCATION 's3://your-bucket/sales-data/';

    SELECT * FROM spectrum_schema.sales
    WHERE order_date > '2024-01-01';

• Can be tuned via Partitioning -defining partitions in the external table to optimize query performance.
• Using columnar formats like Parquet or ORC helps minimize data scans and improves performance.
• When a query is run, it offloads the query execution to Spectrum nodes in the Redshift cluster.
• Scans the data from the S3 files and sends the results back to the Redshift cluster.
• External query processing is done by the Spectrum compute nodes which are separate from the main Redshift cluster, allowing for scalability.
• Leverages massively parallel processing (MPP) for executing queries across both Redshift and external S3 data.
• It pulls in the necessary data from S3 and processes it efficiently to return results.
• Allows you to query massive datasets stored in S3 without moving the data into Redshift, enabling petabyte-scale analytics.
• Pays only for the amount of data scanned by Redshift Spectrum (not the data storage in S3), which can be cost-effective, especially if columnar formats are used and data is partitioned.
• Extends Redshift’s analytics capabilities to data stored in Amazon S3, integrating with data lakes and big data workloads.
• Partitioned data and columnar file formats like Parquet or ORC can significantly improve the performance of queries by reducing the amount of data that needs to be scanned.
• Text-based formats like CSV and TSV are less efficient than Parquet or ORC.
• There might be latency when querying extremely large or unoptimized datasets.
• Can’t perform DML operations (insert, update, delete) on external tables; it’s only for querying data.
• External table metadata must be stored in an external catalog (e.g., AWS Glue or Hive Metastore).
• Queries large amounts of data stored in a data lake on Amazon S3 without needing to move data into Redshift.
• Runs cross-platform queries that combine operational data in Amazon RDS/Aurora with historical data stored in S3.
• Analyzes large volumes of historical data stored in S3 and combines it with current operational data in Redshift.
• Allows running SQL queries on data directly in Amazon S3 without needing to load it into Redshift, integrating and analyzing large datasets.
• Leverages massively parallel processing (MPP) capabilities of Redshift to scale to petabytes of data efficiently, with performance optimization features such as partitioning and columnar formats.

Common System Tables and Views You Should Know for Redshift

• stl_query: Contains information about executed queries (e.g., userid, query, starttime, endtime, state, total_queue_time).
• stl_scan: Tracks table scan activities to understand how much data is being read by each query (e.g., userid, query, table_id, bytes).
• stl_wlm_query: Provides information about query performance in Workload Management (WLM) queues (e.g., queue_start, service_class, slot_count, total_queue_time).
• pg_table_def: Lists the column definitions for tables, including external tables (e.g., tablename, column, type, encoding).
• stl_load_errors: Provides details on errors encountered during data loading operations (e.g., COPY) (e.g., filename, line_number, raw_line, error_type).
• stl_user_activity: Tracks user activity on Redshift, such as data modifications and query executions (e.g., userid, starttime, query, operation).
• stl_explain: Stores the execution plan for each query to help diagnose performance issues (e.g., query, step, seq_scan, cpu_time, total_time).

Amazon Redshift Data API

• Fully managed API that allows interaction without managing database connections directly.
• Provides an easy approach to run SQL queries, retrieve results, and manage transactions through simple HTTP requests.
• Useful for serverless applications or environments where managing database connections is not practical, such as AWS Lambda, API Gateway, or AWS SDK integrations.
• Offers serverless access, eliminating the need to manage or open direct connections to your Redshift cluster.
• Executes SQL statements directly against your Redshift cluster via HTTP requests to the API.
• Supports common SQL commands, including SELECT, INSERT, UPDATE, and DELETE.
• Accessed via simple HTTP requests (e.g., RESTful calls), which are easy to integrate.
• Queries are executed asynchronously.
• Integrates with AWS Lambda, API Gateway, and other AWS services for serverless architectures.
• Integrates with AWS IAM for authentication and access control.
• Returns query results in JSON format, which includes pagination support for large result sets.
• You can ExecuteStatement API, as for example:

        response = client.execute_statement(
            ClusterIdentifier='my-cluster',
            Database='mydatabase',
            SecretArn='arn:aws:secretsmanager:region:account-id:secret:mysecret',
            Sql="SELECT * FROM my_table"
        )

• After execution, the query is processed asynchronously and results can be fetched using the GetStatementResult API call, e.g.:

        result = client.get_statement_result(
            Id='query-id'
        )

• Supports transactions, enabling multiple SQL operations as part of a single transaction.
• Abstracts away the complexity of managing connections.
• Is perfect for serverless architectures like AWS Lambda or API Gateway; easier to integrate with web services, microservices, or automation tasks using the simple HTTP-based API; allows for scalable query execution; integrated IAM ensures secure access.
• Queries in serverless applications like AWS Lambda; microservices to interact; trigger SQL queries based on events; and enable simple web apps to query.
• Simplifies the process of querying Redshift from serverless environments, microservices, or web applications, without requiring persistent database connections, and provides secure, scalable, and asynchronous access.

Amazon Redshift Data Sharing

• Provides secure and efficient sharing of data between Redshift clusters without the need to copy or move data.
• Useful for cross-departmental collaboration, analytics across multiple environments, and sharing data with external systems.
• The data remains in place within the source Redshift cluster.
• Allows for real-time sharing of data between Redshift clusters.
• Allows for granular permission definitions to control what specific data is accessible by the target clusters; integrates with IAM and AWS Identity and Access Management for authentication and authorization.
• Data is not copied or moved between clusters.
• Supports cross-region and cross-account sharing.
• Uses its Massively Parallel Processing (MPP) architecture to allow fast access to shared data.
• Data Sharing is available for Redshift RA3 and DC2 nodes; it requires Redshift Spectrum or Redshift Managed Storage for optimal performance.
• The source cluster exposes data to be shared with one or more consumer clusters.
• Grants access to specific schemas or tables for consumer clusters.
• The consumer then queries the data shared by the provider cluster.
• The consumer cluster does not need to manage or store the data, only to access it via external schemas.
• Creates a data share to specify which tables or views you want to share, e.g.:

CREATE DATASHARE myshare
ADD SCHEMA public
ADD TABLE sales_data;

• The consumer cluster accesses shared data via an external schema that links to the provider cluster's shared data, e.g.:

CREATE EXTERNAL SCHEMA myshare_schema
FROM DATASHARE myshare
ON CLUSTER 'provider-cluster';

• Permissions to access shared data can be granted to specific users or groups; the consumer cluster can query the shared data as if it were local data, but must respect the permissions set by the provider.
• Eliminates the need to copy or replicate data between clusters, reducing storage costs and avoiding data duplication.
• Simplifies the data management process; makes it easier to collaborate across different departments, teams, or even AWS accounts.
• Uses zero-copy sharing, making data available in real time.
• Ensures that sensitive data remains within the original cluster, which can still be shared in a controlled manner with the appropriate permissions.
• Benefits large organizations because different departments can access shared datasets without needing to replicate the data between each department’s Redshift cluster.
• Enables data to be shared with external teams or organizations for reporting and analysis so data remains up to date.
• Allows data to be shared from Redshift to external systems or partners.
• Data Sharing supports cross-region sharing, making it easier for organizations to have data available in different regions for disaster recovery without duplicating it.
• Read only access to shared schema; can be impacted by the network latency, especially in cross-region or cross-account setups.
• Workflow exmaple: In Provider Cluster:

CREATE DATASHARE sales_share
ADD SCHEMA public
ADD TABLE sales_data;

• Grant Access to the Consumer Cluster:

GRANT USAGE ON DATASHARE sales_share TO USER 'consumer_user';

• Create an External Schema in the Consumer Cluster:

CREATE EXTERNAL SCHEMA sales_external
FROM DATASHARE sales_share
ON CLUSTER 'provider-cluster';

• Query the Shared Data in the Consumer Cluster:

SELECT * FROM sales_external.sales_data;

• Provides secure and efficient sharing of live data between Redshift clusters without the need for replication or data copying, with real-time access, cross-account, and cross-region collaboration; while ensuring security and cost savings.

Amazon Redshift Workload Management (WLM)

• Allows you to define and manage how queries are processed within your cluster.
• Helps optimize query performance, ensure fair resource distribution, and improve cluster utilization.
• Manages the workload and prioritizing queries based on resource requirements.
• Allocates resources efficiently, handles concurrency effectively, and controls query execution priorities.