Search the Community

Data is one of the most valuable assets, allowing corporations to make insightful decisions to boost their business performance. By efficiently utilizing their on-premise data, companies are transitioning towards an advanced analytical environment to extract more profound insights. AWS Relational Database Service (RDS) is an Amazon data management web service that can help you manage […]View the full article

Amazon SageMaker Studio’s JupyterLab notebooks now come with a built-in SQL extension with which data scientists can seamlessly discover, explore, and transform data from multiple data sources using SQL and Python right from the notebooks. View the full article

Microsoft Copilot can write basic SQL code, which could help your employees be more efficient and productive.View the full article

ETL processes often involve aggregating data from various sources into a data warehouse or data lake. Bucketing can be used during the transformation phase to aggregate data into predefined buckets or intervals. For example, you might want to aggregate daily sales data into monthly buckets or hourly sensor readings into daily buckets. It plays a […]View the full article

Are you looking to make a career in data science? Start by learning SQL with these free courses.View the full article

Today, we are excited to announce the general availability of Databricks Notebooks on SQL warehouses. Databricks SQL warehouses are SQL-optimized compute that provide... View the full article

In SQL, we have access to the HAVING clause that we can use in conjunction with the GROUP BY clause to filter the rows after grouping. The clause allows us to apply a condition to the result of an aggregate function such as SUM, COUNT, AVG, etc. In this guide, we will explore how we can use the HAVING clause in conjunction with GROUP BY and aggregate function in SQL. SQL HAVING Clause and COUNT() Function In SQL, we use the HAVING clause to filter the results of a GROUP BY query based on a specific condition. We mainly use it in conjunction with aggregate functions like COUNT, SUM, AVG, and MAX to filter the groups of rows that meet a specific criterion. The COUNT function, on the other hand, allows us to count the number of rows in a group. Syntax: The following expresses the syntax of the HAVING clause in conjunction with the COUNT() function: SELECT column1, column2, aggregate_function(COLUMN) AS alias FROM TABLE GROUP BY column1, column2 HAVING aggregate_function(COLUMN) operator VALUE; In the given example, we use the HAVING clause to apply a condition to filter the groups. We then specify the aggregate function which, in this case, is the count() function with the column on which we wish to filter. Examples: Let us explore some practical examples on how to use the HAVING clause in conjunction with the COUNT() function. For demonstration purposes, we will use the Sakila sample database which is freely available to download and use for both MySQL and PostgreSQL. Feel free to use any dataset that you deem applicable in this context. Example 1: Let us assume a scenario where we need to find the “film” categories that contains more than 10 films. WE can use the HAVING COUNT clause as shown in the following: SELECT category.name, COUNT(film.film_id) AS total_films FROM category JOIN film_category ON category.category_id = film_category.category_id JOIN film ON film_category.film_id = film.film_id GROUP BY category.name HAVING COUNT(film.film_id) > 10; In the given example query, we start by selecting the category name and then counting the number of films in each category using the count() function. We then use the GROUP BY clause to group the results based on the category name. Finally, we use the HAVING clause to filter out the categories with a film count that is greater than 10. The resulting table is as follows: name |total_films| -----------+-----------+ Action | 64| Animation | 66| Children | 60| Classics | 57| Comedy | 58| Documentary| 68| Drama | 62| Family | 69| FOREIGN | 73| Games | 61| Horror | 56| Music | 51| NEW | 63| Sci-Fi | 61| Sports | 74| Travel | 57| This should show the number of films in each category. Example 2: Suppose we want to find the actors who have not appeared in any film. We can use a left join and the HAVING COUNT clause as follows: SELECT actor.actor_id, actor.first_name, actor.last_name FROM actor LEFT JOIN film_actor ON actor.actor_id = film_actor.actor_id GROUP BY actor.actor_id, actor.first_name, actor.last_name HAVING COUNT(film_actor.actor_id) = 0; This query filters out the results where the actor has no film in the database. Conclusion In this post, you learned how to use the HAVING clause in conjunction with the SQL COUNT() aggregate function to filter out the results that match a specific condition. We use various Boolean operators such as greater than, less than, etc. to specify various conditions. View the full article

The SQL lead() function allows you to access the next row from the current row at a specific offset. In short, the lead() function allows you to access the next row from the current one. By specifying the offset value, you can access the next 1, 2, 3, etc., rows from the current one. It is the opposite of the lag() function which allows you to access the previous rows. SQL Lead() Function The function syntax is as follows: LEAD(value_expression, offset [, default]) OVER ( [PARTITION BY partition_expression] ORDER BY sort_expression [ASC | DESC] ); The following are the supported arguments: value_expression – It specifies the return value of the preceding row. The expression must evaluate to a single value. offset – It specifies how many rows forward from the current row to access. default – It sets the default value if the offset is outside the scope of the partition. By default, the value is set to NULL. Partition by – It specifies how to partition the data. Order by – It sets the order format for the rows in each partition. Sample Data Setup Before we dive into the workings of the lead() function, let us start by setting up the basic table for demonstration purposes. CREATE TABLE products ( product_id INT PRIMARY KEY AUTO_INCREMENT, product_name VARCHAR(255), category VARCHAR(255), price DECIMAL(10, 2), quantity INT, expiration_date DATE, barcode BIGINT ); insert into products (product_name, category, price, quantity, expiration_date, barcode) values ('Chef Hat 25cm', 'bakery', 24.67, 57, '2023-09-09', 2854509564204); insert into products (product_name, category, price, quantity, expiration_date, barcode) values ('Quail Eggs - Canned', 'pantry', 17.99, 67, '2023-09-29', 1708039594250); insert into products (product_name, category, price, quantity, expiration_date, barcode) values ('Coffee - Egg Nog Capuccino', 'bakery', 92.53, 10, '2023-09-22', 8704051853058); insert into products (product_name, category, price, quantity, expiration_date, barcode) values ('Pear - Prickly', 'bakery', 65.29, 48, '2023-08-23', 5174927442238); insert into products (product_name, category, price, quantity, expiration_date, barcode) values ('Pasta - Angel Hair', 'pantry', 48.38, 59, '2023-08-05', 8008123704782); insert into products (product_name, category, price, quantity, expiration_date, barcode) values ('Wine - Prosecco Valdobiaddene', 'produce', 44.18, 3, '2023-03-13', 6470981735653); insert into products (product_name, category, price, quantity, expiration_date, barcode) values ('Pastry - French Mini Assorted', 'pantry', 36.73, 52, '2023-05-29', 5963886298051); insert into products (product_name, category, price, quantity, expiration_date, barcode) values ('Orange - Canned, Mandarin', 'produce', 65.0, 1, '2023-04-20', 6131761721332); insert into products (product_name, category, price, quantity, expiration_date, barcode) values ('Pork - Shoulder', 'produce', 55.55, 73, '2023-05-01', 9343592107125); insert into products (product_name, category, price, quantity, expiration_date, barcode) values ('Dc Hikiage Hira Huba', 'produce', 56.29, 53, '2023-04-14', 3354910667072); Example 1: In this case, we have access to the “products” table which contains the product information. Suppose we want to get the next bar code from the current row. We can use the lead() function as follows: Suppose we have a table that contains an employee information as follows: select product_name, price, lead(barcode) over (partition by category order by price asc) as next_item from products p; The given code partitions the data based on the category. It then fetches the next barcode in the partition using the lead() function. The resulting output is as follows: Example 2: If there is no next row in a specific column (out of bound), the function sets the value to NULL as shown in the previous example. To set a default value for any out-of-scope access, we can do the following: select product_name, price, lead(barcode, 1, 'N/A') over (partition by category order by price asc) as next_item from products p; We set the default value to “N/A”. This should replace any out-of-bound value as shown in the following output: NOTE: Setting the offset to 1 is similar to not specifying any value. Example 3: Suppose you want to access the next two rows from the current row. We can do that by setting the offset value to 2. An example query is illustrated in the following: select product_name, price, lead(barcode, 2, 'N/A') over (partition by category order by price asc) as next_item from products p; This query returns the next two rows in each partition as shown in the following: There you have it! Conclusion In this tutorial, we learned how to work with the lead() function to get the next item from the current row. View the full article

With code examples on how to use them Continue reading on Towards Data Science » View the full article

Want to become an SQL expert? Check out these free courses to learn and hone your SQL skills for data science.View the full article

Starting today, Amazon RDS Custom for SQL Server supports memory-optimized X2iedn instance types and EBS-optimized R5b instance types. Amazon RDS Custom for SQL Server is a managed database service that allows customization of the underlying operating system and includes the ability to bring your own licensed SQL Server media or use SQL Server Developer Edition. View the full article

Looking to learn SQL and databases to level up your data science skills? Learn SQL, database internals, and much more with these free university courses.View the full article

SQL stands for Structured command language . a question language may be a quite programing language that's designed to facilitate retrieving specific information from databases, and that is exactly what SQL does. to place it simply, SQL is that the language of databases. SQL classes in Pune SQL course in Pune

What is SQLite? What is SQLite SQLite is a self-contained, serverless, and zero-configuration relational database management system (RDBMS). It is a C library that provides a lightweight, disk-based database that doesn’t require a separate server process and allows direct access to the database using a nonstandard variant of the SQL query language. Key features of SQLite include: Self-contained: SQLite is a single ordinary file on the disk that contains the entire database, making it easy to distribute and deploy. There is no essential for a separate server process. Serverless: Unlike traditional RDBMS systems, SQLite doesn’t run as a separate server process. Instead, it is embedded directly into the application that applies it. Zero-Configuration: SQLite doesn’t require any setup or administration. Developers can simply include the SQLite library in their application, and the database is ready to use. Cross-Platform: SQLite is cross-platform and can perform on various operating systems, including Windows, Linux, macOS, and mobile platforms like iOS and Android. Transaction Support: SQLite supports ACID (Atomicity, Consistency, Isolation, Durability) transactions, ensuring data integrity even in the face of system failures. What is top use cases of SQLite? Top use cases of SQLite include: Embedded Systems and IoT Devices: SQLite’s lightweight nature makes it well-suited for embedded systems and IoT devices where resources may be limited. It is commonly used in applications that run on devices with low memory and processing power. Mobile Applications: SQLite is the default database engine for both Android and iOS platforms. Many mobile applications use SQLite for local storage, caching, and managing structured data on the device. Desktop Applications: SQLite is often used in desktop applications, especially those that need a simple, embedded database solution. It’s a good choice for applications that don’t require the complexity of a client-server database system. Small to Medium-Sized Websites: For small to medium-sized websites or web applications with low to moderate traffic, SQLite can serve as a lightweight and easy-to-manage database solution. Prototyping and Development: SQLite is often used during the development and prototyping stages of a project due to its simplicity and ease of use. Developers can quickly set up and work with a SQLite database without the need for complex configurations. Educational Purposes: SQLite is commonly used in educational settings to teach database concepts and SQL, thanks to its simplicity and ease of integration into programming projects. Always keep in mind that while SQLite is a powerful tool for certain use cases, it may not be suitable for large-scale applications with high concurrency and heavy write loads. In such cases, more robust client-server database systems like MySQL, PostgreSQL, or Oracle Database are often preferred. What are feature of SQLite? Features of SQLite SQLite is a lightweight and self-contained relational database management system (RDBMS) with several features that make it suitable for specific use cases. Here are some key features of SQLite: Serverless: SQLite operates without a separate server process. The entire database is contained in a single ordinary file on the disk. Zero-Configuration: SQLite requires minimal setup and administration. There is no need to install and configure a database server. Developers can simply include the SQLite library in their application. Cross-Platform: SQLite is cross-platform and can work on various operating systems, including Windows, Linux, macOS, and mobile platforms like Android and iOS. Self-Contained: The entire database is stored in a single file, making it easy to distribute and deploy. This simplicity is especially useful for embedded systems and applications with limited resources. ACID Transactions: SQLite supports ACID (Atomicity, Consistency, Isolation, Durability) transactions, ensuring data integrity even in the face of system failures. Small Footprint: SQLite has a small memory footprint and is suitable for devices with limited resources. This makes it a good choice for embedded systems and mobile devices. Single User Access: SQLite is designed for single-user access scenarios. While it supports concurrent read access, it doesn’t handle concurrent write access as efficiently as some larger RDBMS designed for multi-user environments. Full SQL Support: SQLite supports a significant subset of the SQL standard, making it compatible with standard SQL queries and commands. What is the workflow of SQLite? Here’s a simplified workflow of using SQLite in an application: Include SQLite Library: Include the SQLite library in your application. This can be done by adding the SQLite library files or using a package manager, depending on the programming language and platform. Database Connection: Open a connection to the SQLite database. This connection is typically established by creating a database file or connecting to an existing one. Table Creation: Define the structure of your database by creating tables. SQLite supports standard SQL syntax for creating tables with columns, data types, and constraints. Data Manipulation: Perform CRUD operations (Create, Read, Update, Delete) on the data in your tables. Use SQL commands or an Object-Relational Mapping (ORM) framework to interact with the database. Transactions: Encapsulate related database operations within transactions to ensure consistency. Begin a transaction, perform the required operations, and then either commit the transaction to make the changes permanent or roll back to discard the changes. Error Handling: Implement error handling to manage potential issues during database interactions. SQLite provides error codes that can be used to diagnose and handle errors programmatically. Close Connection: Close the connection to the SQLite database when it is no longer needed or when the application exits. Always remember that while SQLite is a powerful and flexible solution, its suitability depends on the specific requirements of the application. It excels in scenarios where simplicity, low resource usage, and ease of deployment are crucial. For larger-scale applications with high concurrency and complex requirements, other RDBMS systems might be more appropriate. How SQLite Works & Architecture? SQLite Works & Architecture Here’s an explanation of how SQLite works and its architecture: Core Features: Serverless: SQLite doesn’t require a separate server process, making it lightweight and embedded directly within applications. Self-contained: The entire database engine is contained within a single library file, simplifying distribution and deployment. Single-file database: An entire SQLite database is stored in a single cross-platform file, ensuring portability and ease of management. Dynamic typing: Data types are not strictly enforced, allowing flexibility in data storage and manipulation. Full-featured SQL support: Despite its compact size, SQLite supports most of the SQL standard, enabling complex queries and data manipulation. Architecture: Tokenizer and Parser: Tokenizes SQL statements into syntactic units. Parses the tokens into a parse tree representing the query’s structure. Code Generator: Translates the parse tree into virtual machine instructions for execution. B-tree Pager: Manages low-level disk I/O and database file access. Uses B-tree structures for efficient indexing and data retrieval. Virtual Machine: Executes the generated virtual machine instructions. Interacts with the B-tree pager to access and modify database data. OS Interface: Provides a layer for interacting with the underlying operating system’s file system and memory management. Key Advantages: Zero-configuration: No setup or administration required, making it ideal for embedded systems and mobile apps. Highly portable: SQLite runs on diverse platforms without modifications. Small footprint: Minimal storage requirements and resource consumption. Fast and efficient: Optimized for quick reads and writes, even with large databases. Robust and reliable: Proven track record in a wide range of applications. Common Use Cases: Mobile apps: Storing local app data, user preferences, and offline content. Embedded devices: Handling data management in devices with limited resources. Web browsers: Caching web pages and browsing history. Desktop applications: Saving user settings and preferences. Testing and development: Creating lightweight test databases for application development. How to Install and Configure SQLite? SQLite doesn’t require a separate installation or configuration process in the traditional sense. Here’s how to integrate it into your projects: 1. Obtain the library: Download: Download the precompiled SQLite library file (e.g., sqlite3.dll for Windows, libsqlite3.so for Linux) from the official website. Package manager: If using a programming language with package management (e.g., Python, Java), install the SQLite library using the appropriate command: Python: pip install sqlite3 Java: Add the sqlite-jdbc library to your project’s classpath. 2. Link the library: Development environments: Most development environments have built-in support for linking external libraries. Follow their specific instructions to include the SQLite library in your project. Manual linking: If required, link the library during compilation using appropriate compiler flags (e.g., -lsqlite3 for GCC). 3. Interact with SQLite in your code: APIs: Use the provided API functions for your programming language to interact with SQLite databases: Python: Use the sqlite3 module’s functions. Java: Use the java.sql package for JDBC connections and statements. C/C++: Use the SQLite C API functions. Connection: Establish a connection to a database file (or create a new one if it doesn’t exist). SQL commands: Execute SQL commands for creating tables, inserting data, querying, and modifying data. Important considerations: Version compatibility: Ensure the SQLite library version is compatible with your development environment and programming language version. Cross-platform development: SQLite’s portability makes it easy to use on different platforms without code changes. Command-line interface (CLI): SQLite also comes with a command-line shell for interactive database management and testing. SQLite’s serverless nature means you don’t need to set up or configure a separate database server. It’s ready to use within your application as soon as you integrate the library. Fundamental Tutorials of SQLite: Getting started Step by Step Fundamental Tutorials of SQLite To provide the most effective step-by-step tutorials, I’d need some more information: Your preferred programming language: SQLite works with many languages (Python, Java, C++, etc.). Which one are you using? Your experience level: Are you a beginner to databases in general, or do you have some familiarity with SQL concepts? Your learning style: Do you prefer written tutorials, video lessons, interactive exercises, or a combination? Following is a general outline of common steps involved in fundamental SQLite tutorials, which can be adapted to your specific needs: 1. Getting Started: Download and include the SQLite library: Follow the instructions for your chosen language and development environment. Connect to a database: Learn how to establish a connection to an existing SQLite database file or create a new one. Interact with the database: Use the provided API functions to execute SQL commands and interact with the database. 2. Creating Tables: Define table structure: Learn how to use SQL’s CREATE TABLE statement to define the structure of your tables, including columns and data types. Data types: Understand SQLite’s flexible data typing system and common data types like TEXT, INTEGER, REAL, BLOB, etc. 3. Inserting Data: Add data to tables: Use the INSERT INTO statement to insert new records into your tables. Value placeholders: Learn how to use placeholders to safely insert values into SQL statements. 4. Querying Data: Retrieve and filter data: Use SELECT statements to retrieve specific data from tables based on conditions. Filtering conditions: Employ WHERE clauses to filter results based on criteria. Sorting results: Use ORDER BY to arrange results in ascending or descending order. 5. Updating Data: Modify existing records: Use the UPDATE statement to change values in existing records. Target updates: Specify which records to update using WHERE clauses. 6. Deleting Data: Remove records: Use the DELETE FROM statement to remove unwanted records from tables. Exercise caution: Be mindful of data loss when deleting records. 7. Advanced Features (optional): Transactions: Learn how to group multiple SQL operations into transactions to ensure data consistency. Indexes: Improve query performance by creating indexes on frequently searched columns. Foreign keys: Enforce relationships between tables using foreign keys. SQLite command-line shell: Explore interactive database management using the built-in SQLite shell. I’m eager to provide more specific tutorials once I have a better understanding of your preferences. Feel free to share the details, and I’ll guide you through the process effectively! The post What is SQLite and use cases of SQLite? appeared first on DevOpsSchool.com. View the full article

Amazon Redshift introduces Amazon Q generative SQL in Amazon Redshift Query Editor, an out-of-the-box web-based SQL editor for Redshift, to simplify query authoring and increase your productivity by allowing you to express queries in natural language and receive SQL code recommendations. Furthermore, it allows you to get insights faster without extensive knowledge of your organization’s complex database metadata. View the full article

Access all of Datacamp's 460+ data and AI courses, career tracks & certifications ... https://www.datacamp.com/freeweek

Use this knowledge to upskill yourselves.View the full article

While working with BigQuery for years, I observed 5 issues that are commonly made, even by experienced Data Scientists ... View the full article

We ran a $12K experiment to test the cost and performance of Serverless warehouses and dbt concurrent threads, and obtained unexpected results.By: Jeff Chou, Stewart Bryson Image by Los Muertos CrewDatabricks’ SQL warehouse products are a compelling offering for companies looking to streamline their production SQL queries and warehouses. However, as usage scales up, the cost and performance of these systems become crucial to analyze. In this blog we take a technical deep dive into the cost and performance of their serverless SQL warehouse product by utilizing the industry standard TPC-DI benchmark. We hope data engineers and data platform managers can use the results presented here to make better decisions when it comes to their data infrastructure choices. What are Databricks’ SQL warehouse offerings?Before we dive into a specific product, let’s take a step back and look at the different options available today. Databricks currently offers 3 different warehouse options: SQL Classic — Most basic warehouse, runs inside customer’s cloud environmentSQL Pro — Improved performance and good for exploratory data science, runs inside customer’s cloud environmentSQL Serverless — “Best” performance, and the compute is fully managed by Databricks.From a cost perspective, both classic and pro run inside the user’s cloud environment. What this means is you will get 2 bills for your databricks usage — one is your pure Databricks cost (DBU’s) and the other is from your cloud provider (e.g. AWS EC2 bill). To really understand the cost comparison, let’s just look at an example cost breakdown of running on a Small warehouse based on their reported instance types: Cost comparison of jobs compute, and the various SQL serverless options. Prices shown are based on on-demand list prices. Spot prices will vary and were chosen based on the prices at the time of this publication. Image by author.In the table above, we look at the cost comparison of on-demand vs. spot costs as well. You can see from the table that the serverless option has no cloud component, because it’s all managed by Databricks. Serverless could be cost effective compared to pro, if you were using all on-demand instances. But if there are cheap spot nodes available, then Pro may be cheaper. Overall, the pricing for serverless is pretty reasonable in my opinion since it also includes the cloud costs, although it’s still a “premium” price. We also included the equivalent jobs compute cluster, which is the cheapest option across the board. If cost is a concern to you, you can run SQL queries in jobs compute as well! Pros and cons of ServerlessThe Databricks serverless option is a fully managed compute platform. This is pretty much identical to how Snowflake runs, where all of the compute details are hidden from users. At a high level there are pros and cons to this: Pros: You don’t have to think about instances or configurationsSpin up time is much less than starting up a cluster from scratch (5–10 seconds from our observations)Cons: Enterprises may have security issues with all of the compute running inside of DatabricksEnterprises may not be able to leverage their cloud contracts which may have special discounts on specific instancesNo ability to optimize the cluster, so you don’t know if the instances and configurations picked by Databricks are actually good for your jobThe compute is a black box — users have no idea what is going on or what changes Databricks is implementing underneath the hood which may make stability an issue.Because of the inherent black box nature of serverless, we were curious to explore the various tunable parameters people do still have and their impact on performance. So let’s drive into what we explored: Experiment SetupWe tried to take a “practical” approach to this study, and simulate what a real company might do when they want to run a SQL warehouse. Since DBT is such a popular tool in the modern data stack, we decided to look at 2 parameters to sweep and evaluate: Warehouse size — [‘2X-Small’, ‘X-Small’, ‘Small’, ‘Medium’, ‘Large’, ‘X-Large’, ‘2X-Large’, ‘3X-Large’, ‘4X-Large’]DBT Threads — [‘4’, ‘8’, ‘16’, ‘24’, ‘32’, ‘40’, ‘48’]The reason why we picked these two is they are both “universal” tuning parameters for any workload, and they both impact the compute side of the job. DBT threads in particular effectively tune the parallelism of your job as it runs through your DAG. The workload we selected is the popular TPC-DI benchmark, with a scale factor of 1000. This workload in particular is interesting because it’s actually an entire pipeline which mimics more real-world data workloads. For example, a screenshot of our DBT DAG is below, as you can see it’s quite complicated and changing the number of DBT threads could have an impact here. DBT DAG from our TPC-DI Benchmark, Image by authorAs a side note, Databricks has a fantastic open source repo that will help quickly set up the TPC-DI benchmark within Databricks entirely. (We did not use this since we are running with DBT).To get into the weeds of how we ran the experiment, we used Databricks Workflows with a Task Type of dbt as the “runner” for the dbt CLI, and all the jobs were executed concurrently; there should be no variance due to unknown environmental conditions on the Databricks side. Each job spun up a new SQL warehouse and tore it down afterwards, and ran in unique schemas in the same Unity Catalog. We used the Elementary dbt package to collect the execution results and ran a Python notebook at the end of each run to collect those metrics into a centralized schema. Costs were extracted via Databricks System Tables, specifically those for Billable Usage. Try this experiment yourself and clone the Github repo hereResultsBelow are the cost and runtime vs. warehouse size graphs. We can see below that the runtime stops scaling when you get the medium sized warehouses. Anything larger than a medium pretty much had no impact on runtime (or perhaps were worse). This is a typical scaling trend which shows that scaling cluster size is not infinite, they always have some point at which adding more compute provides diminishing returns. For the CS enthusiasts out there, this is just the fundamental CS principal — Amdahls Law.One unusual observation is that the medium warehouse outperformed the next 3 sizes up (large to 2xlarge). We repeated this particular data point a few times, and obtained consistent results so it is not a strange fluke. Because of the black box nature of serverless, we unfortunately don’t know what’s going on under the hood and are unable to give an explanation. Runtime in Minutes across Warehouse Sizes. Image by authorBecause scaling stops at medium, we can see in the cost graph below that the costs start to skyrocket after the medium warehouse size, because well basically you’re throwing more expensive machines while the runtime remains constant. So, you’re paying for extra horsepower with zero benefit. Cost in $ across Warehouse Sizes. Image by authorThe graph below shows the relative change in runtime as we change the number of threads and warehouse size. For values greater than the zero horizontal line, the runtime increased (a bad thing). The Percent Change in Runtime as Threads Increase. Image by authorThe data here is a bit noisy, but there are some interesting insights based on the size of the warehouse: 2x-small — Increasing the number of threads usually made the job run longer.X-small to large — Increasing the number of threads usually helped make the job run about 10% faster, although the gains were pretty flat so continuing to increase thread count had no value.2x-large — There was an actual optimal number of threads, which was 24, as seen in the clear parabolic line3x-large — had a very unusual spike in runtime with a thread count of 8, why? No clue.To put everything together into one comprehensive plot, we can see the plot below which plots the cost vs. duration of the total job. The different colors represent the different warehouse sizes, and the size of the bubbles are the number of DBT threads. Cost vs duration of the jobs. Size of the bubbles represents the number of threads. Image by authorIn the plot above we see the typical trend that larger warehouses typically lead to shorter durations but higher costs. However, we do spot a few unusual points: Medium is the best — From a pure cost and runtime perspective, medium is the best warehouse to chooseImpact of DBT threads — For the smaller warehouses, changing the number of threads appeared to have changed the duration by about +/- 10%, but not the cost much. For larger warehouses, the number of threads impacted both cost and runtime quite significantly.ConclusionIn summary, our top 5 lessons learned about Databricks SQL serverless + DBT products are: Rules of thumbs are bad — We cannot simply rely on “rules of thumb” about warehouse size or the number of dbt threads. Some expected trends do exist, but they are not consistent or predictable and it is entirely dependent on your workload and data.Huge variance — For the exact same workloads the costs ranged from $5 — $45, and runtimes from 2 minutes to 90 minutes, all due to different combinations of number of threads and warehouse size.Serverless scaling has limits — Serverless warehouses do not scale infinitely and eventually larger warehouses will cease to provide any speedup and only end up causing increased costs with no benefit.Medium is great ?— We found the Medium Serverless SQL Warehouse outperformed many of the larger warehouse sizes on both cost and job duration for the TPC-DI benchmark. We have no clue why.Jobs clusters may be cheapest — If costs are a concern, switching to just standard jobs compute with notebooks may be substantially cheaperThe results reported here reveal that the performance of black box “serverless” systems can result in some unusual anomalies. Since it’s all behind Databrick’s walls, we have no idea what is happening. Perhaps it’s all running on giant Spark on Kubernetes clusters, maybe they have special deals with Amazon on certain instances? Either way, the unpredictable nature makes controlling cost and performance tricky. Because each workload is unique across so many dimensions, we can’t rely on “rules of thumb”, or costly experiments that are only true for a workload in its current state. The more chaotic nature of serverless system does beg the question if these systems need a closed loop control system to keep them at bay? As an introspective note — the business model of serverless is truly compelling. Assuming Databricks is a rational business and does not want to decrease their revenue, and they want to lower their costs, one must ask the question: “Is Databricks incentivized to improve the compute under the hood?” The problem is this — if they make serverless 2x faster, then all of sudden their revenue from serverless drops by 50% — that’s a very bad day for Databricks. If they could make it 2x faster, and then increase the DBU costs by 2x to counteract the speedup, then they would remain revenue neutral (this is what they did for Photon actually). So Databricks is really incentivized to decrease their internal costs while keeping customer runtimes about the same. While this is great for Databricks, it’s difficult to pass on any serverless acceleration technology to the user that results in a cost reduction. Interested in learning more about how to improve your Databricks pipelines? Reach out to Jeff Chou and the rest of the Sync Team. ResourcesTry this experiment yourself and clone the Github repo hereRelated ContentWhy Your Data Pipelines Need Closed-Loop Feedback ControlAre Databricks clusters with Photon and Graviton instances worth it?Is Databricks’s autoscaling cost efficient?Introducing Gradient — Databricks optimization made easy5 Lessons Learned from Testing Databricks SQL Serverless + DBT was originally published in Towards Data Science on Medium, where people are continuing the conversation by highlighting and responding to this story. View the full article

PostgreSQL supports various character types: TEXT, VARCHAR(n), and CHAR(n). The TEXT data type takes an unlimited variable length. VARCHAR(n) takes the values with a variable length without exceeding the set length limit. As for CHAR(n), it takes a fixed length and is blank padded. Understanding CHAR(n) and VARCHAR(n) is essential in understanding the BPCHAR data type. This tutorial discusses everything about BPCHAR. We will understand how CHAR(n) and VARCHAR(n) work and where BPCHAR comes in. Let’s begin! What Is BPCHAR in PostgreSQL When working with any database, you must specify the data type of the columns that you create. That way, the database knows what type of values to expect for a given table. Hence, when you try to insert a value that doesn’t match the expected data type, the database raises an error. In the case of PostgreSQL, BPCHAR is a data type that stands for “blank padded character.” It is associated with the CHAR data type and mainly comes into play when we try to add blank spaces as values. In such a case, the empty spaces are blank-padded to avoid raising any error. To understand BPCHAR in detail, we create a table and see how CHAR and VARCHAR handle different values and where BPCHAR comes into play. In our example, we create a simple table that takes CHAR(n) and VARCHAR(n) where “n” is 10. Here, “n” is the number of characters that can be accommodated. If we inspect our table, we can confirm that all data types and columns are successfully created. Now, let’s start by inserting values into our columns. The values, in this case, have a character length that are less than the specified character length of 10 for each data type. We can verify the length of the inserted values using the length (column-name) option. In the following output, CHAR and VARCHAR have a value with fewer characters than the maximum specified length when creating the table. You should note that the VARCHAR data type only uses the required length since it takes a variable length. However, CHAR takes up all the fixed length regardless of whether the inserted value requires fewer characters. Suppose we try to insert a value that takes more characters than the length of the CHAR data type. We get the following error: The same applies to VARCHAR. Although it has a variable length, it can’t accommodate any value that exceeds the size of the data type. The value that we attempted to insert is larger than 10 characters. Hence, Postgres throws an error which blocks us from inserting the value. To comprehend how BPCHAR works, let’s have an example of inserting the spaces as values and see how CHAR and VARCHAR will treat either case. When you insert the spaces for VARCHAR, it still treats them as characters. Besides, when you insert the spaces that exceed the variable length, it won’t throw any error. But it only takes the maximum length of characters and ignores the rest. In the following example, the value that we insert for VARCHAR is spaces which exceeds to ten characters. The insertion proceeds successfully. When we inspect the inserted value, we see that only ten spaces are captured. Unlike VARCHAR, the CHAR data type treats the spaces differently. It takes them as space-padded characters. Thus, it won’t throw an error but won’t save the blank characters either. Inspecting our table, we see that the CHAR column saved no space, and the length is zero. That’s one element of BPCHAR at work. It treats the blank spaces as padded values. Hence, it doesn’t save them in the database. Going further with spaces, something different happens when we try to insert a value with spaces between characters. For VARCHAR, we can see from the following example that every space is counted as a character. Thus, we get an error that our value is larger than the fixed length for the data type. The same applies to CHAR. Even though it treats the spaces as blank-padded values earlier, when we add them between other characters, it treats them as characters and raises an error when we try adding a longer character length. How about adding spaces between and after characters? How does BPCHAR treat it? In the first example, we added a character that is longer than the required variable length. However, since the last character is spaces, CHAR treats them as blank-padded values and doesn’t throw any error. Inspecting our table, we can see that only the first characters and the spaces between them are accounted for as CHAR values. However, we get a different output if we attempt the same for VARCHAR. It still accounts for the spaces after the characters and records the maximum spaces to fit its fixed-length size. Checking our database, we can note the difference in the length of the characters that are stored as CHAR and VARCHAR. For CHAR, only four characters are stored in the table. However, VARCHAR captured ten characters, including spaces, and ignored the space that exceeded the set fixed length. These examples show how BPCHAR comes into play when working with space-padded values with CHAR. As for VARCHAR, no BPCHAR effect is encountered. Conclusion BPCHAR is a data type that applies when working with CHAR. It makes CHAR treat the spaces as space-padded values that can’t be stored in the table, depending on the location of the space in the value that is being inserted. We’ve seen BPCHAR in detail through the different examples that are presented in this post. Hopefully, you now understand how BPCHAR works and how it affects the characters when working with PostgreSQL. View the full article

Docker Extensions build new functionality into Docker Desktop, extend its existing capabilities, and allow you to discover and integrate additional tools that you’re already using with Docker. More extensions are added every month, so let’s take a look at some of them released in September. And if you’d like to see everything available, check out our full Extensions Marketplace! InterSystems The new InterSystems extension is a convenient way to access InterSystems Container Registry right from Docker Desktop. It provides an integrated UI that contains public and private images for products like IRIS, IRIS for Health, and many more. With the extension, you can: Observe the list of public images available — and private images if you have access to WRC Filter images by name, tag, and ARM64 Easily pull images Delete local images Copy image name with tag Microcks Looking to Mock or test an API? If so, the Microcks extension can help. With Microcks, you can: Mock REST APIs importing local OpenAPI specification or Postman collections Mock GraphQL APIs importing GraphQL Schema and samples via Postman collections Mock gRPC APIs importing Protobuf and samples via Postman collections Simulate event-driven APIs (both on Kafka and WebSockets) importing AsyncAPI specification Test local REST, GraphQL, gRPC, WebSocket and Kafka endpoints to check conformance Bootstrap your API specification using Direct APIs Oracle SQLcl Client Tool Oracle SQLcl (SQL Developer Command Line) is a Java-based command line interface for Oracle Database. Using SQLcl, you can execute SQL and PL/SQL statements in interactive or batch mode. The Oracle SQLcl Client Tool extension provides: Inline editing Statement completion Command recall Support for your existing SQL*Plus scripts Volumes Backup & Share Volumes are the best choice when you need to back up, restore, or migrate data from one Docker host to another. With the Volumes Backup & Share extension, you can: Back up data that’s persisted in a volume (for example, database data from Postgres or MySQL) into a compressed file Upload your backup to Docker Hub and share it with anyone Create a new volume from an existing backup or restore the state of an existing volume Transfer your local volumes to a different Docker host (through SSH) Other basic volume operations like clone, empty, and delete a volume To learn more check out our blog post. Check out the latest Docker Extensions with Docker Desktop Docker is always looking for ways to improve the developer experience. We hope that these new extensions will make your life easier and that you’ll give them a try! Check out these resources for more info on extensions: Try September’s newest extensions by installing Docker Desktop for Mac, Windows, or Linux. Visit our Extensions Marketplace to see all of our extensions Build your own extension with our Extensions SDK View the full article

AWS WAF now supports setting sensitivity levels for SQL injection (SQLi) rule statements, giving you greater control over how AWS WAF evaluates requests to your applications for SQLi attacks. View the full article

IT leaders, engineers, and developers must consider multiple factors when using a database. There are scores of open source and proprietary databases available, and each offers distinct value to organisations. They can be divided into two primary categories: SQL (relational database) and NoSQL (non-relational database). This article will explore the difference between SQL and NoSQL and which option is best for your use case... View the full article

Amazon Relational Database Service (Amazon RDS) Data API can now return results in a new simplified JSON format that makes it easier to convert JSON string to an object in your application. Previously, Amazon RDS Data API returned a JSON string as an array of data type and value pairs. This required developers to write custom code to parse the response and extract the values in order to manually translate the JSON string into an object. Instead, the new format returns an array of column names and values, which makes it easier for common JSON parsing libraries to convert the response JSON string to an object. The previous JSON format is still supported and existing applications using Amazon RDS Data API will work unchanged. To learn more about the new format and how to use it see our documentation. View the full article

The last six months have seen an unprecedented acceleration in digital transformation. There has never been a more important time for database administrators (DBAs) and developers to prepare for an increasingly cloud-centric future. However, we’d like to make this interesting and fun. That’s why we’ve partnered with PASS, a worldwide community of over 300,000 data professionals, to create an interactive learning experience that allows you to test your talent and build your skillset for the future—all while earning to chance to win prizes. Sign up now for the Azure SQL Championship, starting October 12, 2020. View the full article