SQL Tutorial: Storing data скачать в хорошем качестве

SQL Tutorial: Storing data

Want to learn more? Take the full course at https://learn.datacamp.com/courses/da... at your own pace. More than a video, you'll learn hands-on coding & quickly apply skills to your daily work. --- Let's discuss the different ways you can store data. Data can be stored in three different levels. The first is structured data, which is usually defined by schemas. Data types and tables are not only defined, but relationships between tables are also defined, using concepts like foreign keys. The second is unstructured data, which is schemaless and data in its rawest form, meaning it's not clean. Most data in the world is unstructured. Examples include media files and raw text. The third is semi-structured data, which does not follow a larger schema, rather it has an ad-hoc self-describing structure. Therefore, it has some structure. This is an inherently vague definition as there can be a lot of variation between structured and unstructured data. Examples include NoSQL, XML, and JSON, which is shown here on the right. Because its clean and organized, structured data is easier to analyze. However, it's not as flexible because it needs to follow a schema, which makes it less scalable. These are trade-offs to consider as you move between structured and unstructured data. You should already be familiar with traditional databases. They generally follow relational schemas. Operational databases, which are used for OLTP, are an example of traditional databases. Decades ago, traditional databases used to be enough for data storage. Then as data analytics took off, data warehouses were popularized for OLAP approaches. And, now in the age of big data, we need to analyze and store even more data, which is where the data lake comes in. I use the term "traditional databases" because many people consider data warehouses and lakes to be a type of database. Data warehouses are optimized for read-only analytics. They combine data from multiple sources and use massively parallel processing for faster queries. In their database design, they typically use dimensional modeling and a denormalized schema. We will walk through both of these terms later in the course. Amazon, Google, and Microsoft all offer data warehouse solutions, known as Redshift, Big Query, and Azure SQL Data Warehouse, respectively. A data mart is a subset of a data warehouse dedicated to a specific topic. Data marts allow departments to have easier access to the data that matters to them. Technically, traditional databases and warehouses can store unstructured data, but not cost-effectively. Data Lake storage is cheaper because it uses object storage as opposed to the traditional block or file storage. This allows massive amounts of data to be stored effectively of all types, from streaming data to operational databases. Lakes are massive because they store all the data that might be used. Data lakes are often petabytes in size - that's 1,000 terabytes! Unstructured data is the most scalable, which permits this size. Lakes are schema-on-read, meaning the schema is created as data is read. Warehouses and traditional databases are classified as schema-on-write because the schema is predefined. Data lakes have to be organized and cataloged well; otherwise, it becomes an aptly named "data swamp." Data lakes aren't only limited to storage. It's becoming popular to run analytics on data lakes. This is especially true for tasks like deep learning and data discovery, which need a lot of data that doesn't need to be that "clean." Again, the big three cloud providers all offer a data lake solution. When we think about where to store data, we have to think about how data will get there and in what form. Extract Transform Load and Extract Load Transform are two different approaches for describing data flows. They get into the intricacies of building data pipelines, which we will not get into. ETL is the more traditional approach for warehousing and smaller-scale analytics. But, ELT has become common with big data projects. In ETL, data is transformed before loading into storage - usually to follow the storage's schema, as is the case with warehouses. In ELT, the data is stored in its native form in a storage solution like a data lake. Portions of data are transformed for different purposes, from building a data warehouse to doing deep learning. Let's practice! #DataCamp #SQLTutorial #Databasedesign #Storing data