Showing entries 1 to 10 of 125
10 Older Entries »
Displaying posts with tag: data (reset)
Data Masking 101

I continue to dig up and share this simple approach for production data masking via SQL to create testing data sets. Time to codify it into a post.

Rather than generating a set of names and data from tools such as Mockaroo, it is more practical to use actual data for a variety of testing reasons.

The SQL below is a self-explanatory approach of removing Personal Identifiable Information (PII), but keeping data relevant. I use this approach for a number of reasons.

  • We are using production data rather than synthetic data. Data volume, distribution, and additional column values are realistic. This is a subset of an example, but dates and locations are therefore realistic
  • Indexes (and unique indexes) still work, and distribution across the index is adequate for searching. Technically the index …
[Read more]
CSV – SQL Import/Export Compilation

CSV or SQL? SQL or CSV? How about together? Without a doubt, CSV’s are one of the most common and familiar data interchange formats. Importing and exporting CSV data into or out of an SQL database is a staple process in most every data workflow. I’ve written numerous blog posts on both importing and exporting CSV data in an SQL environment. In this post, I am including all of these specific posts (as of the time of writing) in one easy-to-read and centralized location…

Image by xresch from  …

[Read more]
From Spreadsheet to Database with MySQL Workbench

In the last post I covered some of the many reasons to migrate data from a spreadsheet to a database and now it is time to show how to do just that.  Moving data from a spreadsheet into MySQL can actually be pretty simple in many cases. Now I will not mislead you by telling you that all cases are easy but there is an easy way to make this migration.  And I highly recommend the following process when possible as it can save lots of time. More painful migrations will be covered in the future but this post is my favorite 'fast and easy' process.

This is a guide to taking data from a spreadsheet and easily moving that data into a MySQL database instance. The trick is the Import Wizard in MySQL Workbench that does a lot of the detail work for you. In a future post we will go into what you have to do when you are not …

[Read more]
Import CSV file with MySQL Workbench

CSV imports with MySQL Workbench, is super simple. Since CSV’s are probably the most common data interchange format, it goes without saying that importing CSV data into MySQL is a staple task for all DBA’s and Developers. Continue reading to learn how easy it is using MySQL Workbench…

Image by OpenClipart-Vectors from Pixabay

Self-Promotion:

If you enjoy the content written here, by all means, …

[Read more]
MySQL Window Function Compilation

If you use SQL on a regular basis, then you are well aware that Window Functions are powerful. They allow us to simplify queries that would otherwise be quite the mess. We can provide meaningful insight across rows of data without collapsing the results into a single value. I have written numerous blog posts on Window Functions, many here recently. I decided to make this blog post a compilation of all the Window Function posts I have written, providing a one-stop source for any readers interested in learning more about Window Functions…

Image by Free-Photos from …

[Read more]
Top n Window Function queries in MySQL

Top n Window Function queries over a specific subset of data are common in analysis and reporting requirements. Luckily, in MySQL, there are Window functions we can use for this type of query. To be quite honest, you don’t necessarily need Window Functions. You can retrieve those top 3 (or whatever) types of results with a regular SQL query. But, since we have those powerful Window Functions, why not use them? My thoughts exactly! Besides, no one wants a spaghetti code mess of SQL to try and understand. Not to mention, Window functions are often better optimized for querying larger data sets. Continue reading and see example queries for more understanding…

Image by …

[Read more]
Rolling sum and average – Window Functions MySQL

Rolling sum and average query results are possible by combining the aggregate functions SUM() or AVG() with the OVER() clause, making for powerful analytic queries. I recently learned how to compute a rolling average or sum of values by using the Windowing option of the OVER() clause, applying the concepts to a data set I am familiar with. I wanted to share my learning with any readers who might be interested…

Image by Steve Buissinne from …

[Read more]
RANK() and DENSE_RANK() differences

The Window Ranking functions: ROW_NUMBER(), RANK(), and DENSE_RANK() each rank rows with an increasing integer value. I wrote a previous blog post, ROW_NUMBER() Window Function – find duplicate values, where I covered how the ROW_NUMBER() window function can be used to target any duplicate rows, with the use of the PARTITION BY clause in the OVER() clause. In this post, I cover the differences between RANK(), and DENSE_RANK() in handling any ties according to the sorting performed by the ORDER BY clause with regards to the assigned increasing integer. Continue reading and see examples…

[Read more]
Use MySQL UNIQUE Constraint in phpMyAdmin

The MySQL UNIQUE constraint is often used in a column definition in which we need each value for that column to be distinct from the others. Perhaps it is an email column for an on-line registration form and we want to ensure that users cannot register twice for an account using the same email. Whatever the case may be, UNIQUE is there to help us ensure this type of data integrity or business requirement. What if the target table already exists and you determine you need to add a UNIQUE constraint to an existing column? In this post, I will cover 2 ways you can implement a UNIQUE constraint on existing columns using the phpMyAdmin web interface…

Photo by Kaleidico on …

[Read more]
MySQL Shell Python mode for multiple ALTER TABLE statements – easily

There may come a time you need to rename one or more columns in an existing MySQL table for a variety of reasons. Using ALTER TABLE, to rename a column is an easy enough command. But, suppose there are multiple tables in the same database/schema that have the same-named column and all of those columns need to be renamed. That could be a lot of ALTER TABLE statements to type out. Save your energy and time, avoiding all of those ALTER TABLE commands all-together. If you are lucky enough to be working with a MySQL version > 8.0 then the Shell is your salvation. With just a few lines of Python code in \py mode, all of your trouble(s) and headache(s) are no more…

Photo by Ibrahim Rifath on …

[Read more]
Showing entries 1 to 10 of 125
10 Older Entries »