Voice of the DBA

Category: Blog

  • A New Word: Insoucism

    insoucism – n. the inability to decide how much sympathy your situation really deserves, knowing that so many people have it far worse and others far better, that some people would need years of therapy to overcome what you have, while others would barely think to mention it in their diary that day.

    I really struggle to think I deserve sympathy for the challenges I might face in life. I know that I am very lucky and blessed in life, without many of the struggles that people who have less than I experience. I know that most of the time I have #firstworldproblems, which aren’t really problems, but annoyances.

    I needed ankle surgery a few years ago, but really, it wasn’t critical. I could walk, I could bike, I could do yoga inside and snowboard in the winter. I was in some pain, but it wasn’t really a problem that deserves much sympathy.

    I ate and drank way tooooooo much during the pandemic. Changing those habits was a challenge, but it wasn’t something that I think needs sympathy.

    I have some tough travel schedules at times. I’m returning from Kansas City this week, and in the last 5 weeks, I’ve been home for 6 nights. Some of that is vacation with my wife, but a lot of work and travel. I’ve covered easily 30,000 miles in that time. However, it’s not all a burden, and it’s my choice.

    Some people might think that’s an easy life. Some might think this is overwhelming. I think on balance, I don’t have a lot of insourcism because I know that most of my situations don’t deserve sympathy.

    From the Dictionary of Obscure Sorrows

  • Using Flyway with Feature Flags

    There is a nice article at Harness.io on their use of feature flags and how they deployed their next generation experience. It’s worth a read if you want to improve your database deployment experience, especially if you want to control how and when you release to customers.

    Redgate Flyway logo_vertical_RGB_REDA nice bonus is that they mention Flyway as a tool to help them manage database code changes. Hint from me is that you can use Flyway to not only help manage DDL changes but also DML changes as well.

    I talk about this in my Architecting Zero Downtime Database Deployments talk. Most of the time when you have changes that might disrupt users, take a lot of time, or require coordination with different apps/systems, using feature flags and backwards compatible deployments is what makes zero downtime, or minimal downtime, possible. If your changes can’t be backwards compatible, you break the database changes into multiple steps that are backwards compatible.

    Flyway makes it easy to ensure scripts run once, they are deployed as a group or individual transactions, and they run in order, so you can test your scripts in multiple environments.

    I know I sound a little sales-y there, but Flyway is a fantastic tool and I was pro-purchase when Redgate bought the tool. I was also pushing to replace some other internally-developer deployment tech with Flyway in all our products, which we’ve done.

    The big way Flyway supports feature flags is by ensuring you can break up your changes into separate scripts and limit when they run. This is best managed with branches in your VCS and PRs, but this also will be handled by the new Deployment Rules, which are in preview.

  • Writing Parquet Files – #SQLNewBlogger

    Recently I’ve been looking at archiving some data at SQL Saturday, possibly querying it, and perhaps building a data warehouse of sorts. The modern view of data warehousing seems to be built on using a Lakehouse architecture where data moves through different phases, but much of the data is stored in text files, often parquet files.

    As a start to this I decided to try and move data to parquet. This post looks at writing parquet files.

    Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

    Writing Parquet Files

    In a previous post I looked at reading in JSON data, which is how some of my data is archived. I also talked about importing modules. There is a module, called pyarrow, that allows me to work with various parts of Apache Arrow.

    One of the submodules in pyarrow is the parquet module, which lets me read and write parquet files. So, let’s get those modules.

    import pyarrow as pa
import pyarrow.parquet as pq

    I am giving these show names so I can refer to them in code. Now, let’s skip the code from the previous article and assume I’ve got a dataframe with my sessions in it. How do I get a parquet file?

    Fortunately, I don’t need to know anything about the physical structure, as I can use the write_table() function from the parquet module to do that. I’ll also use the pyarrow.Table.from_pandas() function to get data from the dataframe into this module. This code does that (with some setup for a filename).

        outputFilename = f + '.parquet'
    outputFile = join(outPath, outputFilename)
    pqtable = pa.Table.from_pandas(df)
# Write Arrow Table to Parquet file
    pq.write_table(pqtable, outputFile)

    Note: I don’t know the technical differences between how pandas dataframes and the pyarrow tables work. I found a few notes online and it looks like pyarrow tables can handle more complex data structures.

    Once this code is added to the code from the previous article (it’s already indented), this will write .parquet files to the bronze folder underneath the location from where it is run. In essence, this takes data from the raw folder and writes it to bronze in a new format.

    Summary

    This post shows how to write parquet files out from JSON data. Take the previous article and this one and you can move data from JSON to parquet.

    This code isn’t perfect. In fact, it needs work. I am only moving session data, so only a portion of the JSON data. This code should be enhanced, or the file names changed to reflect that, but for now, this is a quick example of producing parquet data.

    SQL New Blogger

    This post took about 10 minutes to write once I had the code working. In fact, adding these functions to the code from the last article only took a few minutes. I had to debug a few things to get the files into the correct folder, but it took longer to get these words down than get code working.

    Not a lot longer, but longer.

    You can do this. If you want to work in modern technologies, learn them. Learn how to work with parquet, which is being used a lot in data warehousing, and then write about it. Prove you can get things done and your current employer, or your next one, might give you a project to actually do this work.

  • A New Word: Flichtish

    flichtish – adj. nervously aware how much of your self-image is based on untested assumptions about yourself – only ever guessing how you’d react to a violent thread, a sudden windfall, a huge responsibility, or being told to do something you knew was wrong..

    I used to think I had all the answers and knew how I’d react to situations or what type of person I was.

    I was a teenager. I think this view is fairly common among teenagers, but also in older people. I see too many people with confidence in how they’d react to an unknown.

    And I see too often they don’t actually react that way. The more self-aware people recognize that their self-image was untested and wrong. The less self-aware people think the situation was an anomaly.

    I know that until I walk a mile in my shoes in some situation, I’m very un-tested. I have a guess as to what my self-image is, but I doubt it is 100% true. As Mike Tyson says, everyone has a plan until they get punched in the mouth. Flichtish is a recognition of that saying.

    From the Dictionary of Obscure Sorrows