Please enable JavaScript.

Coggle requires JavaScript to display documents.

Microsoft Data Fundamental - Coggle Diagram

- - - - On Azure, large-scale data ingestion is best implemented by creating pipelines that orchestrate ETL processes. Creating and run pipelines using Azure Data Factory, or use the same pipeline engine in Azure Synapse Analytics if you want to manage all of the components of your data warehousing solution in a unified workspace.
      - In either case, pipelines consist of one or more activities that operate on data. An input dataset provides the source data, and activities can be defined as a data flow that incrementally manipulates the data until an output dataset is produced. Pipelines use linked services to load and process data – enabling you to use the right technology for each step of the workflow.
  - - - Azure Synapse Analytics is a unified, end-to-end solution for large scale data analytics. It brings together multiple technologies and capabilities, enabling you to combine the data integrity and reliability of a scalable, high-performance SQL Server based relational data warehouse with the flexibility of a data lake and open-source Apache Spark
      - Azure Databricks is an Azure implementation of the popular Databricks platform. Databricks is a comprehensive data analytics solution built on Apache Spark, and offers native SQL capabilities as well as workload-optimized Spark clusters for data analytics and data science.
      - Azure HDInsight is an Azure service that supports multiple open-source data analytics cluster types. Although not as user-friendly as Azure Synapse Analytics and Azure Databricks, it can be a suitable option if your analytics solution relies on multiple open-source frameworks or if you need to migrate an existing on-premises Hadoop-based solution to the cloud.
    - - In batch processing, newly arriving data elements are collected and stored, and the whole group is processed together as a batch. Exactly when each group is processed can be determined in a number of ways. For example, you can process data based on a scheduled time interval (for example, every hour), or it could be triggered when a certain amount of data has arrived, or as the result of some other event.
      - In stream processing, each new piece of data is processed when it arrives. Unlike batch processing, there's no waiting until the next batch processing interval - data is processed as individual units in real-time rather than being processed a batch at a time. Stream data processing is beneficial in scenarios where new, dynamic data is generated on a continual basis.
    - - Real time analytics
        
        Azure Stream Analytics: A platform-as-a-service (PaaS) solution that you can use to define streaming jobs that ingest data from a streaming source, apply a perpetual query, and write the results to an output.
        
        Spark Structured Streaming: An open-source library that enables you to develop complex streaming solutions on Apache Spark based services, including Azure Synapse Analytics, Azure Databricks, and Azure HDInsight.
        
        Azure Data Explorer: A high-performance database and analytics service that is optimized for ingesting and querying batch or streaming data with a time-series element, and which can be used as a standalone Azure service or as an Azure Synapse Data Explorer runtime in an Azure Synapse Analytics workspace.
        
        Explore Apache Spark on Microsoft Azure
        
        Apache Spark is a distributed processing framework for large scale data analytics. You can use Spark on Microsoft Azure in the following services:Azure Synapse Analytics, Azure Databricks, Azure HDInsight
        
        To process streaming data on Spark, you can use the Spark Structured Streaming library, which provides an application programming interface (API) for ingesting, processing, and outputting results from perpetual streams of data.
        
        Delta Lake is an open-source storage layer that adds support for transactional consistency, schema enforcement, and other common data warehousing features to data lake storage. It also unifies storage for streaming and batch data, and can be used in Spark to define relational tables for both batch and stream processing. When used for stream processing, a Delta Lake table can be used as a streaming source for queries against real-time data, or as a sink to which a stream of data is written.
      - sources for stream pcrocessing
        
        Azure Event Hubs: A data ingestion service that you can use to manage queues of event data, ensuring that each event is processed in order, exactly once.
        
        Azure IoT Hub: A data ingestion service that is similar to Azure Event Hubs, but which is optimized for managing event data from Internet-of-things (IoT) devices.
        
        Azure Data Lake Store Gen 2: A highly scalable storage service that is often used in batch processing scenarios, but which can also be used as a source of streaming data.
        
        Apache Kafka: An open-source data ingestion solution that is commonly used together with Apache Spark. You can use Azure HDInsight to create a Kafka cluster.
      - Sinks for stream processing
        
        Azure Event Hubs: Used to queue the processed data for further downstream processing.
        
        Azure Data Lake Store Gen 2 or Azure blob storage: Used to persist the processed results as a file.
        
        Azure SQL Database or Azure Synapse Analytics, or Azure Databricks: Used to persist the processed results in a database table for querying and analysis.
        
        Microsoft Power BI: Used to generate real time data visualizations in reports and dashboards.
    - - Definition: Azure Stream Analytics is a service for complex event processing and analysis of streaming data.
        
        Uses:
        
        Ingest data from an input, such as an Azure event hub, Azure IoT Hub, or Azure Storage blob container.
        
        Process the data by using a query to select, project, and aggregate data values.
        
        Write the results to an output, such as Azure Data Lake Gen 2, Azure SQL Database, Azure Synapse Analytics, Azure Functions, Azure event hub, Microsoft Power BI, or others.
      - Azure Stream Analytics jobs and clusters
        
        The easiest way to use Azure Stream Analytics is to create a Stream Analytics job in an Azure subscription, configure its input(s) and output(s), and define the query that the job will use to process the data
    - - Definition: Apache Spark is a distributed processing framework for large scale data analytics. You can use Spark on Microsoft Azure in the following services:
        
        Azure Synapse Analytics
        
        Azure Databricks
        
        Azure HDInsight
      - Spark Structured Streaming
        
        To process streaming data on Spark, you can use the Spark Structured Streaming library, which provides an API for ingesting, processing, and outputting results from perpetual streams of data.
        
        Spark Structured Streaming is built on a ubiquitous structure in Spark called a dataframe, which encapsulates a table of data. You use the Spark Structured Streaming API to read data from a real-time data source, such as a Kafka hub, a file store, or a network port, into a "boundless" dataframe that is continually populated with new data from the stream. You then define a query on the dataframe that selects, projects, or aggregates the data - often in temporal windows. The results of the query generate another dataframe, which can be persisted for analysis or further processing.
      - Delta Lake
        
        Definition: Delta Lake is an open-source storage layer that adds support for transactional consistency, schema enforcement, and other common data warehousing features to data lake storage. It also unifies storage for streaming and batch data, and can be used in Spark to define relational tables for both batch and stream processing. When used for stream processing, a Delta Lake table can be used as a streaming source for queries against real-time data, or as a sink to which a stream of data is written.
  - - - Definition: Microsoft Power BI is a suite of tools and services that data analysts can use to build interactive data visualizations for business users to consume.
      - After you've created data models and reports, you can publish them to the Power BI service
      - You can also do some basic data modeling and report editing directly in the service using a web browser, but the functionality for this is limited compared to the Power BI Desktop tool
      - You can also define dashboards and apps that combine related reports in a single, easy to consume location.
      - Users can consume reports, dashboards, and apps in the Power BI service through a web browser, or on mobile devices by using the Power BI phone app.
- - - - Partitioning is a mechanism for grouping related rows, based on a common property or partition key
      - Rows that share the same partition key will be stored together
      - Partitions are independent from each other, and can grow or shrink as rows are added to, or removed from, a partition. A table can contain any number of partitions.
    - - a partition key that identifies the partition containing the row
      - a row key that is unique to each row in the same partition
  - - - Page blobs. A page blob is organized as a collection of fixed size 512-byte pages. A page blob is optimized to support random read and write operations; you can fetch and store data for a single page if necessary. A page blob can hold up to 8 TB of data. Azure uses page blobs to implement virtual disk storage for virtual machines.
      - Append blobs. An append blob is a block blob optimized to support append operations. You can only add blocks to the end of an append blob; updating or deleting existing blocks isn't supported. Each block can vary in size, up to 4 MB. The maximum size of an append blob is just over 195 GB.
      - Block blobs. A block blob is handled as a set of blocks. Each block can vary in size, up to 100 MB. A block blob can contain up to 50,000 blocks, giving a maximum size of over 4.7 TB. The block is the smallest amount of data that can be read or written as an individual unit. Block blobs are best used to store discrete, large, binary objects that change infrequently
    - - The Hot tier is the default. You use this tier for blobs that are accessed frequently. The blob data is stored on high-performance media
      - The Cool tier. This tier has lower performance and incurs reduced storage charges compared to the Hot tier. Use the Cool tier for data that is accessed infrequently
      - The Archive tier. This tier provides the lowest storage cost, but with increased latency. The Archive tier is intended for historical data that mustn't be lost, but is required only rarely. Blobs in the Archive tier are effectively stored in an offline state. Typical reading latency for the Hot and Cool tiers is a few milliseconds, but for the Archive tier, it can take hours for the data to become available
  - - - Migrate existing applications to the cloud.
      - Share server data across on-premises and cloud.
      - Integrate modern applications with Azure File Storage.
      - Simplify hosting High Availability (HA) workload data.
  - - - SQL API. This interface provides a SQL
      - Table API. This interface enables you to use the Azure Table Storage API to store and retrieve documents
      - MongoDB API. MongoDB is another well-known document database, with its own programmatic interface
      - Cassandra API. Cassandra is a column family database management system
      - Gremlin API. The Gremlin API implements a graph database interface to Cosmos DB
      - Documents in a Cosmos DB database are organized into containers. The documents in a container are grouped together into partitions. A partition holds a set of documents that share a common partition key
      - Benefits of using Azure Cosmos DB
        
        Cosmos DB is a highly scalable database management system. Cosmos DB automatically allocates space in a container for your partitions, and each partition can grow up to 10 GB in size. Indexes are created and maintained automatically. There's virtually no administrative overhead
        
        All databases are replicated within a single region. This replication is transparent, and failover from a failed replica is automatic. Cosmos DB guarantees 99.99% high availability
        
        You can choose to replicate data across regions, at additional cost. This feature enables you to place copies of data anywhere in the world, and enable applications to connect to the copy of the data that happens to be the closest, reducing query latency. All replicas are synchronized
        
        Cosmos DB guarantees less than 10-ms latencies for both reads (indexed) and writes at the 99th percentile, all around the world
        
        Cosmos DB is certified for a wide array of compliance standards. Additionally, all data in Cosmos DB is encrypted at rest and in motion
      - Usecases
        
        IoT and telematics
        
        Retail and marketing
        
        Gaming
        
        Web and mobile applications
- - - - Definition: Normalization is a term used by database professionals for a schema design process that minimizes data duplication and enforces data integrity.
        
        Separate each entity into its own table.
        
        Separate each discrete attribute into its own column.
        
        Uniquely identify each entity instance (row) using a primary key.
        
        Use foreign key columns to link related entities.
    - - Definition: SQL stands for Structured Query Language, and is used to communicate with a relational database. It's the standard language for relational database management systems. SQL statements are used to perform tasks such as update data in a database, or retrieve data from a database. Some common relational database management systems that use SQL include Microsoft SQL Server, MySQL, PostgreSQL, MariaDB, and Oracle
      - Some popular dialects of SQL
        
        Transact-SQL (T-SQL). This version of SQL is used by Microsoft SQL Server and Azure SQL services.
        
        pgSQL. This is the dialect, with extensions implemented in PostgreSQL.
        
        PL/SQL. This is the dialect used by Oracle. PL/SQL stands for Procedural Language/SQL.
      - SQL statement types
        
        DDL statements
        
        Definition: You use DDL statements to create, modify, and remove tables and other objects in a database (table, stored procedures, views, and so on).
        
        DCL statements
        
        Definition: Database administrators generally use DCL statements to manage access to objects in a database by granting, denying, or revoking permissions to specific users or groups.
        
        DML statements
        
        Definition: You use DML statements to manipulate the rows in tables. These statements enable you to retrieve (query) data, insert new rows, or modify existing rows. You can also delete rows if you don't need them anymore.
    - - What is a view?
      - What is a stored procedure?
      - What is an index?
  - - - Azure SQL
        
        Definition: is a collective term for a family of Microsoft SQL Server based database services in Azure. Specific Azure SQL services include:
        
        SQL Server on Azure Virtual Machines (VMs)
        
        Definition: SQL Server on Virtual Machines enables you to use full versions of SQL Server in the Cloud without having to manage any on-premises hardware. This is an example of the IaaS approach.
        
        SQL Server running on an Azure virtual machine effectively replicates the database running on real on-premises hardware. Migrating from the system running on-premises to an Azure virtual machine is no different than moving the databases from one on-premises server to another.
        
        This approach is suitable for migrations and applications requiring access to operating system features that might be unsupported at the PaaS level. SQL virtual machines are lift-and-shift ready for existing applications that require fast migration to the cloud with minimal changes. You can also use SQL Server on Azure VMs to extend existing on-premises applications to the cloud in hybrid deployments.
        
        You can use SQL Server in a virtual machine to develop and test traditional SQL Server applications. With a virtual machine, you have the full administrative rights over the DBMS and operating system. It's a perfect choice when an organization already has IT resources available to maintain the virtual machines.
        
        These capabilities enable you to:
        
        Create rapid development and test scenarios when you don't want to buy on-premises non-production SQL Server hardware.
        
        Become lift-and-shift ready for existing applications that require fast migration to the cloud with minimal changes or no changes.
        
        Scale up the platform on which SQL Server is running, by allocating more memory, CPU power, and disk space to the virtual machine. You can quickly resize an Azure virtual machine without the requirement that you reinstall the software that is running on it.
        
        Business benefits:
        
        Running SQL Server on virtual machines allows you to meet unique and diverse business needs through a combination of on-premises and cloud-hosted deployments, while using the same set of server products, development tools, and expertise across these environments.
        
        Azure SQL Managed Instance
        
        Definition: Azure SQL Managed instance effectively runs a fully controllable instance of SQL Server in the cloud. You can install multiple databases on the same instance. You have complete control over this instance, much as you would for an on-premises server. SQL Managed Instance automates backups, software patching, database monitoring, and other general tasks, but you have full control over security and resource allocation for your databases. You can find detailed information at What is Azure SQL Managed Instance?.
        
        Usercase: Consider Azure SQL Managed Instance if you want to lift-and-shift an on-premises SQL Server instance and all its databases to the cloud, without incurring the management overhead of running SQL Server on a virtual machine.
        
        benifit: Azure SQL Managed Instance enables a system administrator to spend less time on administrative tasks because the service either performs them for you or greatly simplifies those tasks. Automated tasks include operating system and database management system software installation and patching, dynamic instance resizing and configuration, backups, database replication (including system databases), high availability configuration, and configuration of health and performance monitoring data streams.
        
        Azure SQL Database
        
        Definition: Azure SQL Database is a PaaS offering from Microsoft. You create a managed database server in the cloud, and then deploy your databases on this server.
        
        Single Database: This option enables you to quickly set up and run a single SQL Server database. You create and run a database server in the cloud, and you access your database through this server. Microsoft manages the server, so all you have to do is configure the database, create your tables, and populate them with your data.
        
        Elastic Pool: his option is similar to Single Database, except that by default multiple databases can share the same resources, such as memory, data storage space, and processing power through multiple-tenancy. The resources are referred to as a pool. You create the pool, and only your databases can use the pool. This model is useful if you have databases with resource requirements that vary over time, and can help you to reduce costs.
        
        Usecase: Azure SQL Database gives you the best option for low cost with minimal administration. It isn't fully compatible with on-premises SQL Server installations. It's often used in new cloud projects where the application design can accommodate any required changes to your applications.
        
        Benifit:
        
        Azure SQL Database automatically updates and patches the SQL Server software to ensure that you're always running the latest and most secure version of the service.
        
        The scalability features of Azure SQL Database ensure that you can increase the resources available to store and process data without having to perform a costly manual upgrade.
        
        The service provides high availability guarantees, to ensure that your databases are available at least 99.99% of the time. Azure SQL Database supports point-in-time restore, enabling you to recover a database to the state it was in at any point in the past. Databases can be replicated to different regions to provide more resiliency and disaster recovery
        
        Azure SQL Edge
    - - MySql
        
        MySQL started life as a simple-to-use open-source database management system. It's the leading open source relational database for Linux, Apache, MySQL, and PHP (LAMP) stack apps. It's available in several editions; Community, Standard, and Enterprise. The Community edition is available free-of-charge, and has historically been popular as a database management system for web applications, running under Linux. Versions are also available for Windows. Standard edition offers higher performance, and uses a different technology for storing data. Enterprise edition provides a comprehensive set of tools and features, including enhanced security, availability, and scalability. The Standard and Enterprise editions are the versions most frequently used by commercial organizations, although these versions of the software aren't free.
        
        Azure Database for MySQL is a PaaS implementation of MySQL in the Azure cloud, based on the MySQL Community Edition.
        
        The Azure Database for MySQL service includes high availability at no additional cost, and scalability as required. You only pay for what you use. Automatic backups are provided, with point-in-time restore.
        
        The server provides connection security to enforce firewall rules and, optionally, require SSL connections. Many server parameters enable you to configure server settings such as lock modes, maximum number of connections, and timeouts.
        
        Azure Database for MySQL has two deployment options: Single Server and Flexible Server.
        
        Azure Database for MySQL Flexible Server is a fully managed database as a service offering, with predictable performance and dynamic scalability. Flexible Server provides more granular control and flexibility over database management functions and configuration settings. Flexible server is the recommended deployment option for all new developments or migrations.
        
        Azure Database for MySQL Single Server is a fully managed database as a service offering, with predictable performance and dynamic scalability. Single servers are best for existing applications already using single server.
        
        benefits
        
        High availability features built-in.
        
        Predictable performance.
        
        Easy scaling that responds quickly to demand.
        
        Secure data, both at rest and in motion.
        
        Automatic backups and point-in-time restore for the last 35 days.
        
        Enterprise-level security and compliance with legislation.
      - MariaDB
        
        MariaDB is a newer database management system, created by the original developers of MySQL. The database engine has since been rewritten and optimized to improve performance. MariaDB offers compatibility with Oracle Database (another popular commercial database management system). One notable feature of MariaDB is its built-in support for temporal data. A table can hold several versions of data, enabling an application to query the data as it appeared at some point in the past.
        
        Benifit
        
        Built-in high availability with no additional cost.
        
        Predictable performance, using inclusive pay-as-you-go pricing.
        
        Scaling as needed within seconds.
        
        Secured protection of sensitive data at rest and in motion.
        
        Automatic backups and point-in-time-restore for up to 35 days.
        
        Enterprise-grade security and compliance.
      - PostgreSQL
        
        PostgreSQL is a hybrid relational-object database. You can store data in relational tables, but a PostgreSQL database also enables you to store custom data types, with their own non-relational properties. The database management system is extensible; you can add code modules to the database, which can be run by queries. Another key feature is the ability to store and manipulate geometric data, such as lines, circles, and polygons.
        PostgreSQL has its own query language called pgsql. This language is a variant of the standard relational query language, SQL, with features that enable you to write stored procedures that run inside the database.
        
        Deployment options
        
        Single Server
        
        The single-server deployment option for PostgreSQL provides similar benefits as Azure Database for MySQL. You choose from three pricing tiers: Basic, General Purpose, and Memory Optimized. Each tier supports different numbers of CPUs, memory, and storage sizes; you select one based on the load you expect to support.
        
        Flexible Server
        
        The flexible-server deployment option for PostgreSQL is a fully managed database service. It provides more control and server configuration customizations, and has better cost optimization controls.
        
        Hyperscale
        
        Hyperscale (Citus) is a deployment option that scales queries across multiple server nodes to support large database loads. Your database is split across nodes. Data is split into chunks based on the value of a partition key or sharding key. Consider using this deployment option for the largest database PostgreSQL deployments in the Azure Cloud.
        
        Benifit
        
        Azure Database for PostgreSQL is a highly available service. It contains built-in failure detection and failover mechanisms.
        
        Users of PostgreSQL will be familiar with the pgAdmin tool, which you can use to manage and monitor a PostgreSQL database. You can continue to use this tool to connect to Azure Database for PostgreSQL. However, some server-focused functionality, such as performing server backup and restore, aren't available because the server is managed and maintained by Microsoft.
        
        Azure Database for PostgreSQL records information about queries run against databases on the server, and saves them in a database named azure_sys. You query the query_store.qs_view view to see this information, and use it to monitor the queries that users are running. This information can prove invaluable if you need to fine-tune the queries performed by your applications.
- - - - Azure SQL
        
        Azure SQL Database
        
        Azure SQL Managed Instance
        
        Azure SQL VM
      - Azure Database for open-source relational databases
        
        Azure Database for MySQL
        
        Azure Database for MariaDB
        
        Azure Database for PostgreSQL
      - Azure Cosmos DB
        
        Azure Cosmos DB is a global-scale non-relational (NoSQL) database system that supports multiple application programming interfaces (APIs), enabling you to store and manage data as JSON documents, key-value pairs, column-families, and graphs.
      - Azure Storage
        
        Blob containers
        
        File shares
        
        Tables
      - Azure Data Factory
        
        Definition: Azure Data Factory is an Azure service that enables you to define and schedule data pipelines to transfer and transform data. You can integrate your pipelines with other Azure services, enabling you to ingest data from cloud data stores, process the data using cloud-based compute, and persist the results in another data store.
      - Azure Synapse Analytics
        
        Definition: Azure Synapse Analytics is a comprehensive, unified data analytics solution that provides a single service interface for multiple analytical capabilities, including
        
        Pipelines
        
        SQL
        
        Apache Spark
        
        Azure Synapse Data Explorer
      - Azure Databricks
        
        Definition: Azure Databricks is an Azure-integrated version of the popular Databricks platform, which combines the Apache Spark data processing platform with SQL database semantics and an integrated management interface to enable large-scale data analytics.
      - Azure HDInsight
        
        Apache Spark
        
        Apache Hadoop
        
        Apache HBase
        
        Apache Kafka
        
        Apache Storm
      - Azure Stream Analytics
        
        Azure Stream Analytics is a real-time stream processing engine that captures a stream of data from an input, applies a query to extract and manipulate data from the input stream, and writes the results to an output for analysis or further processing.
      - Azure Data Explorer
        
        Azure Data Explorer is a standalone service that offers the same high-performance querying of log and telemetry data as the Azure Synapse Data Explorer runtime in Azure Synapse Analytics.
      - Azure Purview
        
        Azure Purview provides a solution for enterprise-wide data governance and discoverability. You can use Azure Purview to create a map of your data and track data lineage across multiple data sources and systems, enabling you to find trustworthy data for analysis and reporting
      - Microsoft Power BI
        
        Microsoft Power BI is a platform for analytical data modeling and reporting that data analysts can use to create and share interactive data visualizations. Power BI reports can be created by using the Power BI Desktop application, and the published and delivered through web-based reports and apps in the Power BI service, as well as in the Power BI mobile app.