<?xml version="1.0" encoding="UTF-8"?><rss xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:content="http://purl.org/rss/1.0/modules/content/" xmlns:atom="http://www.w3.org/2005/Atom" version="2.0" xmlns:cc="http://cyber.law.harvard.edu/rss/creativeCommonsRssModule.html">
    <channel>
        <title><![CDATA[Stories by Vlad Gheorghe on Medium]]></title>
        <description><![CDATA[Stories by Vlad Gheorghe on Medium]]></description>
        <link>https://medium.com/@vlad.publish?source=rss-abd2f990eeab------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*tUKhdIb6lx66DOuZriOVQQ.jpeg</url>
            <title>Stories by Vlad Gheorghe on Medium</title>
            <link>https://medium.com/@vlad.publish?source=rss-abd2f990eeab------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Tue, 17 Nov 2026 08:36:29 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@vlad.publish/feed" rel="self" type="application/rss+xml"/>
        <webMaster><![CDATA[yourfriends@medium.com]]></webMaster>
        <atom:link href="http://medium.superfeedr.com/" rel="hub"/>
        <item>
            <title><![CDATA[Get Started With BigQuery For Free In 2022]]></title>
            <link>https://medium.com/learning-sql/get-started-with-bigquery-for-free-in-2022-fcbe487ab4c?source=rss-abd2f990eeab------2</link>
            <guid isPermaLink="false">https://medium.com/p/fcbe487ab4c</guid>
            <category><![CDATA[google-cloud-platform]]></category>
            <category><![CDATA[data]]></category>
            <category><![CDATA[sql]]></category>
            <category><![CDATA[bigquery]]></category>
            <category><![CDATA[cloud]]></category>
            <dc:creator><![CDATA[Vlad Gheorghe]]></dc:creator>
            <pubDate>Wed, 18 May 2022 15:27:08 GMT</pubDate>
            <atom:updated>2023-12-03T19:10:15.901Z</atom:updated>
            <content:encoded><![CDATA[<h4>A hands-on introduction</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1000/0*vxtYnhvqo790UV68" /><figcaption>Picture by <a href="https://dlxmedia.hu/">https://dlxmedia.hu/</a> on <a href="https://unsplash.com/photos/rigSOqqWZrw">Unsplash</a>.</figcaption></figure><p><em>This article was last updated on May 2022. I cannot guarantee that it will be current at the time of reading. I invite you to do your own research, especially when it comes to pricing.</em></p><h3>Introduction</h3><p>In this post I explain how to set up BigQuery on your personal account and start using it without spending anything.</p><p>I also explain the fundamental concepts that you cannot do without if you want to work with BigQuery. These include:</p><ul><li>What is a Google Cloud Platform project and how to create one</li><li>How data is organized in BigQuery</li><li>How BigQuery’s pricing works</li><li>How to connect to a public dataset</li><li>How to exploit BigQuery’s UI</li><li>Writing, executing and exporting the results of your first query</li><li>A primer for writing fast, cost-efficient queries</li></ul><p><strong>There are no knowledge requirements for this post</strong>, although a basic experience with SQL is helpful.</p><h3>What is BigQuery?</h3><p>BigQuery is Google’s solution for the modern data warehouse. <strong>A data warehouse is a system designed to store and analyze large quantities of data</strong> (think terabytes and petabytes).</p><p><strong>BigQuery works with SQL</strong>, so you don’t need to learn a special language for it. Data is organized in tables, which are collections of rows and columns.</p><p>Whether you are a data analyst, data scientist or data engineer, you can leverage BigQuery’s impressive capabilities to achieve your objectives. For example, <a href="https://cloud.google.com/blog/products/bigquery/anatomy-of-a-bigquery-query">you can analyse 100 billion rows or 7 terabytes of data in 30 seconds</a>. Not something you can do with a traditional database.</p><p><strong>BigQuery is serverless.</strong> You don’t have to worry about providing servers, clusters or virtual machines. And you don’t need to perform <a href="https://en.wikipedia.org/wiki/Database_administration">database administration</a> tasks like defining indexes, sort keys or tuning performance. All this is automatically managed for you. Fire up the service, load some data and start analysing.</p><p>However, BigQuery does have a learning curve, especially if you’ve never worked with a data warehouse before.</p><h3>Disclaimer</h3><p>BigQuery is essentially a paid tool (more details on pricing in a bit). However, it has both a free tier and a sandbox mode, that will allow you to test it for free.</p><p><strong>If you ignore all my advice and try <em>really</em> hard, you <em>could</em> end up wasting money on it</strong>. I will explain in detail how the pricing works and how to avoid spending a single cent. I suggest you follow my instructions carefully. But ultimately, you are responsible for how you use the service. <strong>I don’t take responsibility for any billing incidents!</strong></p><p>With that out of the way, we are ready to get started.</p><h3>Projects and billing</h3><p>BigQuery is part of <a href="https://www.google.com/aclk?sa=l&amp;ai=DChcSEwjsxOTR1s_3AhWyjWgJHUyQANAYABAAGgJ3Zg&amp;sig=AOD64_0kX53mQMwwlcKpND0ySehpcoUkWw&amp;q&amp;adurl&amp;ved=2ahUKEwjkutrR1s_3AhXw_7sIHWnWCRAQ0Qx6BAgDEAE">Google Cloud Platform</a> (from now on GCP), a suite of cloud services provided by Google for computing, databases, data workflows and AI. For a high level overview, see this cheat sheet describing <a href="https://github.com/priyankavergadia/google-cloud-4-words#the-google-cloud-developers-cheat-sheet">every Google Cloud service in four words or less</a>.</p><p>The first step in using a GCP service is to create a project. <strong>Everything that happens in GCP, happens inside a project. Each project is connected to a billing account,</strong> which carries the owner’s payment information. Projects allow GCP to manage pricing and ensure that users pay for services.</p><p>Since we want to get started for free, there are multiple ways that we can avoid paying:</p><ul><li>BigQuery has a sandbox mode which allows monthly free allowance.</li><li>BigQuery also has a free tier which is essentially equivalent to sandbox mode.</li><li>When you sign up to GCP for the first time, you should get $300 of free credits, which you can use to try out paid services.</li><li>At the end of the article, I show how to setup billing controls and alerts to make sure you avoid bad surprises.</li></ul><p>Let’s go ahead and create our first project.</p><h3>Creating a project</h3><p>You will need a Google account for this. If you don’t have one yet, <a href="https://www.google.com/account/about/">you can create it here</a>.</p><p>Go to the <a href="https://cloud.google.com/">Google Cloud homepage</a>. If you don’t have a GCP account yet, select “Get started for free” in the upper right corner. Go through the steps for creating your account.</p><p>The last step requires you create a payments profile. Make sure you select “Individual” under “Account type”. You will need to select a payment method so GCP can verify your identity. At the time of writing this can be either a debit card, a credit card or a PayPal account. No worries: you are not yet liable to get billed at this point.</p><p>You may encounter two issues here:</p><ol><li><strong>If you don’t have a debit card,credit card, or a PayPal account, you won’t be able to get started</strong>. I know this is a serious problem for some users, but I don’t know of any alternative solutions. As long as you have a bank account, you should be able to get a free card or a PayPal account.</li><li><strong>GCP may simply refuse your payment method.</strong> This has happened to me a few times. At the moment, GCP is not letting me use PayPal. Unfortunately there is no way of knowing why this happens. The only thing that worked for me was to use another payment method. I suggest you try different methods until you find one that works.</li></ol><p>Once you‘re signed up, you will be able to access the GCP dashboard at this link: <a href="https://console.cloud.google.com/home/dashboard">https://console.cloud.google.com/home/dashboard</a> (make sure you are logged in with the right Google account).</p><p>Now it’s time to create a project. The dashboard should prompt you to create one, otherwise you can do it at <a href="https://console.cloud.google.com/projectcreate">this link</a>. Choose a name for your project and click on “create”.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/889/1*V9tNyyS_S8KeBigDrZOhTg.png" /></figure><h3>Accessing BigQuery</h3><p>You should now see your project’s dashboard. Your project’s name is displayed in the blue navigation bar at the top. You can access BigQuery by writing in the search bar at the top or opening the navigation menu in the upper left corner and scrolling down until you find it:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/662/1*iAYBmA66nirAkCsQIs272g.png" /></figure><p>I recommend clicking on the pin icon on the right to access it more easily next time.</p><p>You should now see the BigQuery UI. See the “Sandbox” banner on top? This assures us that we are using BigQuery for free.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Ez1kQqje6BiI9MJIra7qew.png" /></figure><h3>How BigQuery organizes data</h3><p>Data is organized as follows:</p><ul><li>Every GCP project has a number of <strong>datasets.</strong></li><li>A dataset is a collection of <strong>tables.</strong></li><li>A table is a collection of rows and columns, where the data lives.</li></ul><p>Concretely, a BigQuery table looks like an <a href="https://www.w3schools.com/sql/sql_intro.asp">SQL table</a>, a <a href="https://pandas.pydata.org/">Pandas DataFrame</a>, or a spreadsheet.</p><p>In BigQuery, every table has a unique address in the form &lt;project&gt;.&lt;dataset&gt;.&lt;table&gt;. For example my-project.orders.table1.</p><p>You may occasionally see the old notation my-project:orders.table1 (colon after the project) but know that this is deprecated.</p><p>Once you open BigQuery, you can see any data available to you in the Explorer tab on the left. This is currently empty because we did not load any data.</p><p>To see how data loading works, click on the three dots next to your project and select “Create dataset”.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/605/1*m0ivQd6JwcYFjpQ5Ib7lUw.png" /></figure><p>Insert a Dataset ID. This should be something informative like order_data. Be sure to click on “Data location” and select a location that is close to you. This is where all the data within the dataset will be stored.</p><p>Once you create your dataset, you will see it displayed in the Explorer tab. You can now add your first table.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/578/1*3Od9bSZpu5Ic4a3W_PGo6w.png" /></figure><p>In the dialog that follows you can choose a name for your table, among other options. In the Source section, you will see there are several options to load your data into this table. However, we will not explore those options here. Instead, we will leverage Google’s public datasets so we can start querying immediately.</p><h3>Connect to BigQuery public datasets</h3><p><a href="https://cloud.google.com/bigquery/public-data">Bigquery’s public datasets</a> are a product of Google’s initiative to store public data and make it generally available. Let’s see how we can access them.</p><p>In the search bar on the Explorer tab, write “public” and then click on “Broaden search to all projects”. Note: you must literally write just “public” and nothing else. Other queries don’t work for me!</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/449/1*chOs_6JZmojrOZ55xymiCA.png" /></figure><p>You should now see the bigquery-public-data project. Click on the pin icon to save it in Explorer.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/446/1*d8MEiW1XGZlREsanCrUv2g.png" /></figure><p>Refresh the UI webpage. Now Explorer shows two projects: your personal project and bigquery-public-data. If you expand the latter, you will see that it contains a number of different datasets. Click on a dataset and you will see that it contains one or more tables.</p><p>This interaction between different projects may be confusing, so let’s clarify what’s happening. First of all, keep in mind that <strong>we are still working from our personal project</strong>. Everything we do happens in there.</p><p>But Google has their own project on GCP, which is called bigquery-public-data. <strong>Since the data in this project is public, we can access it from our personal project.</strong></p><p>It’s good to understand this because if you work with BigQuery, it will be fairly common to access data from different projects at the same time. Of course, you can only access data from public projects or from private projects where you’ve been explicitly authorized.</p><p>Here’s how the division of labor works between these two projects:</p><ul><li>Google’s project stores and manages the data. Since the data is residing there, <strong>they also pay for the storage</strong>.</li><li>Your personal project queries the data. Since you are reading their data, <strong>you pay for the query costs</strong>.</li><li>If you wanted to <strong>copy</strong> their data, you would need to store the copy in your personal project, and you would then pay storage costs for that.</li></ul><p>In short: <strong>the project where the data resides pays for storage, the project that queries the data pays for querying.</strong></p><p>Before we start querying, let’s get some insight into how <strong>BigQuery pricing</strong> works.</p><h3>BigQuery’s pricing model</h3><p>BigQuery’s pricing is simple. You pay for two things:</p><ul><li>The data that is scanned for your queries. This is also known as <strong>analysis pricing.</strong></li><li>The data that is stored in your project. This is known as <strong>storage pricing.</strong></li></ul><p>Analysis pricing is usually the main cost driver. Let’s explore it in detail.</p><p>Whenever you send a query, BigQuery scans a certain quantity of data to give you the result. The data scanned is measured in bytes and billed accordingly. <strong>At the time of writing, you pay approximately $5 for every terabyte (TB) scanned</strong>. In addition, <strong>the first terabyte for every month is free, </strong>thanks to the free tier pricing. There is a minimum charge of 10 MB. You are only charged if your query is successful: if it fails, there is no charge.</p><p>Let’s take a simple example. You have a table, orders, which contains exactly 1 TB of data. You write the following query:</p><pre>SELECT * FROM orders</pre><p>This query asks BigQuery to return all the data in orders. To do this, BigQuery has to scan the whole table. How much data will be scanned by this query? 1 TB. And how much will this query cost you? Currently, about $5. But thanks to free tier pricing, you get to do this once a month for free.</p><p>Now I must address a common pitfall. <strong>Analysis pricing targets the total bytes scanned, not the bytes returned</strong>. This is an important distinction. Imagine you write this query:</p><pre>SELECT * FROM orders LIMIT 1</pre><p>This query returns a single row. You might think that it would cost much less. Actually, <strong>it costs as much as the previous one</strong>. Why? Because <strong>BigQuery still scans the whole table</strong>, even though it only returns one row. Since the pricing depends on bytes scanned, not on bytes returned, the price is the same.</p><p>It helps to have an understanding of how queries run in the backend to avoid falling for such pitfalls. Fortunately, <strong>BigQuery always gives you an estimate of a query’s cost before you run it</strong>, so if you’re careful you won’t have any bad surprises.</p><p>If you think the price is too high, don’t worry. There are many ways to keep costs under control:</p><ul><li>Before you run a query, BigQuery tells you how many bytes it expects to scan. You can thus get an estimate of the price and decide if it’s worth it.</li><li>You can set spending limits on queries so that a query that exceeds your budget will simply not run.</li><li>The documentation provides several <a href="https://cloud.google.com/bigquery/docs/best-practices-costs">BigQuery best practices</a> that allow you to query efficiently and control costs.</li></ul><p>We explore all these tricks in our example below.</p><p>The second cost factor is storage. At the time of writing, the monthly expense for storing 1TB of data is about $20. You can store 10 GB for free.</p><p>To reiterate: BigQuery’s free tier gives you a free monthly allowance for storing 10 GB and scanning 1 TB. The sandbox is basically the same as the free tier, except that you can use it even without making a billing account.</p><p>Most other operations in BigQuery, such as creating tables and importing/exporting data, are free.</p><p>You can find more details on pricing in the <a href="https://cloud.google.com/bigquery/pricing">official documentation</a>.</p><h3>Exploiting the BigQuery UI</h3><p>There is much we can learn about our data before we even start querying (and spending).</p><p>In the Explorer tab, click on the bigquery-public-data project. Scroll down until you find the google-trends dataset (or use the search bar). Once you’ve opened the dataset, click on the table international_top_rising_terms. You should see the table panel in the Editor.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*SdD3-IOnOJvwHB-_epDB1w.png" /></figure><p>This panel is your best friend when working on BigQuery. It has three tabs: schema, details and preview.</p><p><strong>Schema.</strong> Shows the columns in your table. Each column has a name and a data type. This is equivalent to an SQL schema.</p><p><strong>Details.</strong> Here you find useful information such as the table address, its size, location, number of rows and when it was last updated.</p><p><strong>Preview.</strong> Shows you a few rows from the table.</p><p>The preview is crucial because <strong>it allows you to get a sense of the data without running any queries</strong>.</p><p>If you’ve worked with SQL databases before, you might be tempted to start your analysis by running a SELECT * LIMIT 10 type of statement to return the first few rows. There’s no point in doing that in BigQuery, as you would incur unnecessary costs. Just use the preview tab.</p><h3>Our first query</h3><p>We are ready to run our first query. But first, a warning:</p><p><strong>Never run a query before you’ve made sure that you’re okay with the costs. Once you hit RUN, you will incur the query’s costs.</strong></p><p>The BigQuery UI helps us here. Whenever we write a query, in the upper right corner we can see how many bytes it’s expected to scan.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/566/1*Duj_wJnhibk7oFTkMD4Low.png" /></figure><p>In this case my query will process 47 MB. Since 1 TB = 1,000,000 MB, this represents less than 0.01% of the free monthly allowance. I can run it without any worries.</p><p>Here’s another trick. Once you write a query, click on More in the upper panel and then Query Settings. Scroll to the bottom, select Advanced options and you will see an option for maximum bytes billed. Here you can define the maximum amount of bytes that you are willing to be charged for. If your query scans more than that, it will stop running and you won’t be charged.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*_lXfQcCMX1MFqvaTNxTCqw.png" /></figure><p>Time to explore our data. The <a href="https://cloud.google.com/blog/products/data-analytics/top-25-google-search-terms-now-in-bigquery">Google Trends dataset</a> shows each day’s 25 top Google search terms, both in U.S. and abroad, broken down to small geographical areas (regions or cities).</p><p>We are interested in finding the top 25 search terms in the United Kingdom.</p><p>The table we want is bigquery-public-data.google_trends.international_top_terms .</p><p>One of BigQuery’s quirks is that the table address needs to be surrounded by <a href="https://en.wikipedia.org/wiki/Backtick">backticks</a> when writing a query.</p><p>We can now write our query:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/51f9bc8a1cf7618687f73ce747900bc9/href">https://medium.com/media/51f9bc8a1cf7618687f73ce747900bc9/href</a></iframe><p>Here’s a breakdown:</p><ul><li><strong>FROM</strong>. We access the table by defining project, dataset and table name, and enclosing the address in backticks.</li><li><strong>WHERE. </strong>We select records where refresh_date is yesterday. We also filter for records in the United Kingdom.</li><li><strong>GROUP BY</strong>. We aggregate by term and rank.</li><li><strong>AVG(score). </strong>We extract the term’s average score in each U.K. region. The score indicates the term’s popularity relative to other terms in that region. Averaged across all regions, the score indicates how widespread that term is (is it popular in just one region, or in all of them?).</li><li><strong>ORDER BY. </strong>We order by rank ascending, so the most important terms come on top.</li></ul><p>As I am writing this, the query above is estimated to scan 277 MB of data. Yet… If I select the table in the UI and go to the Details panel, I see that the table contains approximately 15 GB of data. My query is scanning about 2% of all the table data, which makes it quite cheap. How come is it so efficient?</p><p>I am actually using some BigQuery magic to minimize costs. More on this in a minute.</p><p>We can now run the query. Here’s my results:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*4herBPNBya7zLLZPaoeJ3g.png" /></figure><p>You can click on “Save results” to export this data in a variety of formats. In fact, you should do it. There’s no reason to run the same query more than once!</p><p>Before we wrap up, let’s examine how we can keep our queries fast and cheap.</p><h3>Writing efficient queries</h3><p>Keep this definition in mind:</p><blockquote><em>An efficient query is one that scans the least amount of data, in the least amount of time, while still achieving its goals.</em></blockquote><p>Remember, the number of bytes scanned by a query determines its cost. Therefore we are always incentivized to keep our queries efficient. Another nice effect of reducing the bytes scanned is that our query will run faster.</p><p>In the query above, I managed to scan only 2% of the total table data. I used two tricks to do this:</p><ul><li>I selected only the columns that I needed.</li><li>I used a partition filter.</li></ul><p>Let’s see how you can apply these tricks to your own queries.</p><p><strong>Select only the columns you need.</strong> BigQuery uses <a href="https://en.wikipedia.org/wiki/Column-oriented_DBMS">columnar storage</a>. It divides a table by columns, rather than rows, for purposes of storage and querying. Therefore a great way to save costs is to select only the columns that you need. Avoid SELECT *, which returns all the columns. You can test this in the editor by seeing how the query cost changes when adding or removing columns in the SELECT statement.</p><p><strong>Apply partition filters.</strong> Partitioning is a way to divide a table into distinct segments. When done right, this allows for more efficient querying. If you click the google_trends.international_top_terms table and select Details in the table panel, you will see that the table is partitioned on the field refresh_date. Furthermore, it is partitioned by DAY.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/544/1*z6QW7cjpkEwbNhnzWuNlbQ.png" /></figure><p>What does this mean? Through partitioning, the data for each distinct day, as indicated by refresh_date, is stored in a separate segment. As a consequence, <strong>when you filter by </strong><strong>refresh_date in your queries, you will only scan the data that fits in that timespan</strong> (as opposed to scanning the entire table).</p><p>To understand this, compare the following queries, but <strong>DON’T RUN THEM</strong>. You only need to paste them in the editor and check the expected cost!</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/77187f5c31c0dc92269935fe929e20fe/href">https://medium.com/media/77187f5c31c0dc92269935fe929e20fe/href</a></iframe><p>You can see that if you filter by refresh_date, each additional day adds 400-500 MB to your query costs. If you don’t use the partioning filter, you are scanning the entire table.</p><p>When working on a table, it’s always a good idea to check if it’s partitioned so you can exploit this optimization. You can see if and how a table is partitioned in the table panel under the Details tab.</p><p>Trick: if you click on the query icon in the table panel, BigQuery will open a tab with a pre-written query that leverages the partitioned field.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*fGiTX8MxcY2ZXNHLZZCdMA.png" /></figure><p>For more details on partitioning check the <a href="https://cloud.google.com/bigquery/docs/partitioned-tables">documentation</a>.</p><p>Google has more extensive documentation on <a href="https://cloud.google.com/bigquery/docs/best-practices-performance-overview">how to improve query performance</a>.</p><h3>Conclusion</h3><p>Well done! You’ve setup BigQuery in your personal project, leveraged Google’s public datasets, wrote your first query and learned the fundamentals of BigQuery in the process.</p><p>I hope that you feel empowered to start working and experimenting with this awesome tool.</p><p>If you have any questions, feel free to drop a comment. You can also find me on <a href="https://www.linkedin.com/in/vlad-ds/">LinkedIn</a>.</p><h3>Bonus: Activate Billing</h3><p>At some point, you might want to get out of sandbox mode, either because you need to overcome its limitations, or because you have exhausted the free allowance. Here’s how you can do it.</p><p>From the GCP search bar or Navigation menu, select the Billing service. Then select “Manage billing accounts”. Click the button to create a billing account. Afterwards, you should see an overview of your billing account. On the left panel, click on “Budgets and alerts” and select “Create budget”. Give a name to your budget and click next.</p><p>Now you can specify a maximum spending target for each month. In my case I chose 5 euros.</p><p><strong>Note: this will not establish a hard limit on your spending.</strong> It is purely an alerting device, so you will know if you are exceeding your spending target and can take appropriate measures.</p><p>In the final step, you can decide which thresholds will trigger the alert. By default, you’ll get an email when you hit 50%, 90% and 100% of your total budget.</p><p>Now that your billing account is ready, you need to link it to your personal project. Go back to BigQuery and click on Upgrade in the upper right corner. Select your billing account and you’re done.</p><p>You are now out of sandbox mode and your account is billable for BigQuery. But you still have the free tier privileges from the sandbox: you can store 10 GB of data and query 1 TB monthly for free (as of the time of writing).</p><p>If you still end up spending money on top of the free tier, you can still use your your $300 of free signup credits.</p><p><em>Thank you for reading. Want to receive my next stories? </em><a href="https://www.linkedin.com/in/vlad-ds/"><em>Follow me on LinkedIn</em></a><em>.</em></p><p><strong>Go from SELECT * to interview-worthy project. </strong><a href="https://learning-sql.ck.page/aa03293372"><strong>Get our free 5-page guide</strong></a><strong>.</strong></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=fcbe487ab4c" width="1" height="1" alt=""><hr><p><a href="https://medium.com/learning-sql/get-started-with-bigquery-for-free-in-2022-fcbe487ab4c">Get Started With BigQuery For Free In 2022</a> was originally published in <a href="https://medium.com/learning-sql">Learning SQL</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Software Developer Jobs in Berlin: A Textual Analysis]]></title>
            <link>https://medium.com/swlh/software-developer-jobs-in-berlin-an-overview-66f0c8d26026?source=rss-abd2f990eeab------2</link>
            <guid isPermaLink="false">https://medium.com/p/66f0c8d26026</guid>
            <category><![CDATA[selenium]]></category>
            <category><![CDATA[data-science]]></category>
            <category><![CDATA[glassdoor]]></category>
            <category><![CDATA[scraping]]></category>
            <category><![CDATA[jobs]]></category>
            <dc:creator><![CDATA[Vlad Gheorghe]]></dc:creator>
            <pubDate>Sat, 10 Oct 2020 14:45:35 GMT</pubDate>
            <atom:updated>2020-10-12T14:56:56.047Z</atom:updated>
            <content:encoded><![CDATA[<h3>Software Developer Jobs in Berlin: A Textual Anaylsis</h3><h4>Scraping and analyzing job entries from Glassdoor.</h4><h3>Introduction</h3><h4>Motivation</h4><p>Landing the first job in the IT industry is not easy. As I moved to Berlin and started browsing through job entries, I figured I might benefit from a few data-driven insights. Here is a brief report of what I’ve learned.</p><h4>Scraping Jobs with Selenium</h4><p>For my data source, I selected the popular jobs site <a href="https://www.glassdoor.com/member/home/index.htm">Glassdoor</a>. Since their API is not directly accessible, I had to scrape the jobs on my own. The process was not simple. I had to access the website, login with my details, and examine every job posting in turn.</p><p>I knew that Selenium WebDriver was the tool for the job. I found a <a href="https://github.com/natmod/glassdoor-scrape">relevant project</a> on GitHub, but it didn’t work for me. (Hardly surprising, as the code is two years old, and popular websites change all the time). So I wrote a new script from scratch. It takes job title and location as input and executes the search.</p><p>You can access the code at my <a href="https://github.com/vlad-ds/glassdoor-scrape">GitHub</a>.</p><h3>The data</h3><p>On September 4, 2020 I extracted 834 job entries. I used the search term ‘software developer’ and set the location to Berlin. For each job I collected the title, description, and the name and rating of the company.</p><p>The data and the notebook with the analysis are available at my <a href="https://github.com/vlad-ds/glassdoor-scrape">GitHub</a>.</p><h3>Insights</h3><h4>1. German vs. English</h4><p>Some postings were in English, others in German. To proceed with the text analysis, I had to equalize them. Moreover, I asked myself: how important is German knowledge in the context of these jobs?</p><p>Fortunately, Google Cloud provides free access to its translating service through the ‘Free trial credit’ program. I implemented the <a href="https://cloud.google.com/translate/docs/quickstarts">Google Translate API</a> and used it to translate all the postings. Before translating, the service detects the original language. Thus, I could see that a majority of postings were written in English.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*2LtBuzayUT_GcgqoKIuhcQ.png" /><figcaption>Figure 1. Language of the job description.</figcaption></figure><p>However, there is a selection effect at work, as my search query was in English. In fact, at least 38% of the job descriptions mention the German language. The importance of knowing German should not be underestimated.</p><h4>2. Companies</h4><p>Berlin’s IT scene is <a href="https://medium.com/berlin-beyond-borders/berlins-rising-tech-startup-scene-bacf7fd6b0e">quite rich</a>. For this dataset, I identified 432 distinct companies. The company with most postings was Amazon, with 31 entries. The other companies had no more than 11 entries each.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/775/1*khH3-5AuXbQXMLhBcu3tKg.png" /><figcaption>Figure 2. Companies word cloud. Size is proportional to the number of entries.</figcaption></figure><p>Glasdoor provides a rating from 1 to 5 for most companies. By looking at the rating distribution, we obtain a general assessment of the job satisfaction in this domain.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Tp3A4dSCdVdBSP0RMIszXw.png" /><figcaption>Figure 3. Companies’ ratings distribution.</figcaption></figure><p>The distribution is biased towards higher ratings. This suggests that the employees are generally satisfied with these companies.</p><h4>3. Job titles</h4><p>‘Software developer’ is a broad term. To gain more insight into the nature of the jobs, we can look at the word cloud for job titles.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/775/1*r1UUQ0C__9WG8RIw4v4Qxw.png" /><figcaption>Figure 4. Word cloud for job titles.</figcaption></figure><p>I was surprised by the preponderance of DevOps jobs. Looking at the qualifications appearing in job titles, it seems that DevOps is favored.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*hZUplsyqueYFrkv-Y0y6jw.png" /><figcaption>Figure 5. DevOps is mentioned often in job titles.</figcaption></figure><p>If we look at seniority qualifications, we find that almost 300 job titles ask for seniors, while only 28 mention juniors or students.</p><p>My guess is that the majority of positions want someone who’s in the middle — neither too green nor too ripe. More on that below.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Uu-RZB4Fdo5C2ZCBKIILLw.png" /><figcaption>Figure 6. Seniority qualifications in job titles.</figcaption></figure><h4>4. Technologies</h4><p>IT jobs are strongly defined by the technologies which the applicant is required to understand. To study which technologies were included in my data, I parsed the job descriptions for upper-cased words. I examined the words which were mentioned in at least 20 descriptions and selected those which represented a specific technology. I then mapped each word to a universal referent to avoid confusions and repetitions (e.g. Go and Golang are the same technology). In the end I obtained a list of relevant technologies.</p><p>I then scored each technology on the number of job descriptions in which it appeared.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*m4tbyuAx1g-36WnVHs4vkg.png" /><figcaption>Figure 7. Technologies by mentions in job descriptions.</figcaption></figure><p>JavaScript, Java and SQL were the most popular technologies, followed by AWS and Docker. Python was only sixth — not very surprising, given that most of these jobs are not data-related.</p><p>If we look at technologies mentioned in job titles, Java is by far the most popular. PHP is second, despite its shadow of decline, and Python is third.</p><p>After this analysis, I am even stronger in my resolve to learn React. It’s the only framework that has 15 job titles all to itself!</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*kavhoygCzLTGyj29eI1ydg.png" /><figcaption>Figure 8. Technologies by mention in job titles.</figcaption></figure><h4>5. Experience</h4><p>How many years of experience are typically required? This is a crucial question. I didn’t have that information, so I had to extract it from the descriptions. To this purpose, I looked for “n-years expressions”. These are parts of a text where we find the word ‘years’ preceded by a number.</p><p>From a qualitative analysis, I concluded that “n-years expressions” serve mainly three functions in job descriptions: they indicate the duration of the contract, they relate to the history of the company, or they refer to the years of experience. I was only interested in the latter, so I selected expressions where the subsequent 10 words included the word ‘experience’.</p><p>The numerical expressions sometimes indicated ranges, such as ‘5–7 years’ or ‘5+ years’. In that case, I selected the lower end of the range (5 in both examples). To filter out false positives, I manually verified that no description asked for more than 10 years.</p><p>Some descriptions had more than one “n-years expressions”: for example, a job may ask for 5 years experience in Java and 2 years in Go. I wanted to find the binding conditions for obtaining the job, so I selected the highest requirement.</p><p>Overall, this method allowed me to identify the required years of experience for 245 jobs.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*vFuUpTUKWFigjQkVskZBoQ.png" /><figcaption>Figure 9. Required years of experience.</figcaption></figure><p>Most jobs require between 3 and 5 years of experience. From the cumulative plot, we see that around 50% of the jobs require at least 3 years of experience.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*1z_axpXsQ8S7mZl6MpiPGQ.png" /><figcaption>Figure 10. Required years of experience (cumulative).</figcaption></figure><p>However, this should not be too disheartening for beginners. Most likely, recruiters who are willing to accept less years of experience are not going to state it explicitly.</p><h3>Findings</h3><p>This is a preliminary analysis and there is plenty of opportunity for more investigations.</p><p>Here are the findings so far:</p><ol><li>At least a third of the jobs place importance on the German language.</li><li>A great number of companies are posting jobs in Berlin. Their employees ratings are generally high.</li><li>DevOps is more popular than I expected as far as specializations go.</li><li>Most jobs require between 3 and 5 years of experience. The majority of positions don’t mention seniority qualifications explicitly.</li><li>If you don’t know which languages to study, Java, JavaScript and SQL are your best bets. If you have more time, study AWS and Docker. Picking JavaScript and React is a strong move.</li></ol><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=66f0c8d26026" width="1" height="1" alt=""><hr><p><a href="https://medium.com/swlh/software-developer-jobs-in-berlin-an-overview-66f0c8d26026">Software Developer Jobs in Berlin: A Textual Analysis</a> was originally published in <a href="https://medium.com/swlh">The Startup</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Build A Newsletter Unsubscribe Tool]]></title>
            <link>https://medium.com/@vlad.publish/build-a-newsletter-unsubscribe-tool-9aefec644af2?source=rss-abd2f990eeab------2</link>
            <guid isPermaLink="false">https://medium.com/p/9aefec644af2</guid>
            <category><![CDATA[coding]]></category>
            <category><![CDATA[python]]></category>
            <category><![CDATA[privacy]]></category>
            <category><![CDATA[email]]></category>
            <dc:creator><![CDATA[Vlad Gheorghe]]></dc:creator>
            <pubDate>Mon, 10 Feb 2020 10:14:16 GMT</pubDate>
            <atom:updated>2020-02-10T10:14:16.581Z</atom:updated>
            <content:encoded><![CDATA[<h4>With just a few lines of Python.</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*k6N8KrXJZpfTHvXT2vTlUg.jpeg" /><figcaption>Photo by <a href="https://unsplash.com/@krystagrusseck?utm_source=unsplash&amp;utm_medium=referral&amp;utm_content=creditCopyText">Christa Dodoo</a> on <a href="https://unsplash.com/s/photos/paper?utm_source=unsplash&amp;utm_medium=referral&amp;utm_content=creditCopyText">Unsplash</a></figcaption></figure><p>The New Year is a time for cleaning up, and the email inbox should be no exception.</p><p>In this spirit, I’ve been looking for a tool that would help me unsubscribe in bulk from undesired newsletters. Inevitably, I found my way to <em>Unroll.me</em>. However, the service is <a href="https://techcrunch.com/2018/05/05/unroll-me-to-close-to-eu-users-saying-it-cant-comply-with-gdpr/">not available in Europe</a> — which is a good thing, if it’s true that it was <a href="https://www.theverge.com/2019/12/17/21027159/unroll-me-email-privacy-ftc-settlement">selling user information</a> to Uber and other companies.</p><p>Ironically, the services who should protect us from unwanted intrusion can be more dangerous than the problems they claim to solve.</p><p>Well, one way you can make sure a service is completely safe is to build it yourself.</p><p>Of course, it’s also a great learning experience. Recently, I’ve become a fan of <a href="https://joelgrus.com/">Joel Grus</a>. Although I can’t match the elegance of his code, I share his philosophy: if you really want to understand how something works, you should build it from scratch.</p><p>Accordingly, for this task I’ve used no more than a couple regular expressions and a single Pandas import (which is not essential at all). Overall, the script is about 30 lines long.</p><p>The project was done on the Gmail MBOX export, but it could be easily extended to other systems.</p><h4>Requirements</h4><p>For this project, you simply need Python 3. I’ve also used Pandas to generate the CSV file. You can easily install it from the command line:</p><pre>pip install pandas. </pre><h4>Downloading your emails</h4><p>The first thing is to download a copy of your emails. Go on <a href="https://takeout.google.com/">Google Takeout</a>and scroll down until you find Gmail and select it for the export. You get to choose what kind of emails to include in the download. I went ahead and downloaded all of them.</p><p>When Google sends you the data (it should take no more than a few hours) open the archive and download the MBOX file in your working folder. For simplicity, I renamed it ‘<em>mail.mbox’</em>.</p><h4>Extracting the emails</h4><p>The MBOX is basically a text file. We use Python’s native functions to extract a list of all our mails:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/104b7d8f9d58ae2371fc3cf0aae77c63/href">https://medium.com/media/104b7d8f9d58ae2371fc3cf0aae77c63/href</a></iframe><p>We parse the file line by line, and add every line to the <em>mail</em> string. Whenever a line starts with <em>‘From’</em>, it marks the beginning of a new mail. So we take all the lines we assembled before that and save them as a single message. Then we start again with the <em>‘From’</em> line we just found. The first object of our list will be redundant, because there is no text to assemble before that. So we discard it.</p><h4>Extracting the unsubscribe links</h4><p>We’re going to use the ‘List-Unsubscribe’ field in our mails. Business senders are <a href="https://www.zettasphere.com/gmail-email-unsubscribe-header/">strongly encouraged</a> to use this field. When available, it includes an URL, a MAILTO address or both, which allow users to unsubscribe with one click.</p><p>The next function reads every email, extracts the name of the sender, checks if the ‘List-Unsubscribe’ field is available, and then saves the unsubscribe links. If that particular sender has already been recorded, each additional mail adds to its count — so we can see who is sending the most emails.</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/2fc910ff84070d9bea717b89d3bc9abc/href">https://medium.com/media/2fc910ff84070d9bea717b89d3bc9abc/href</a></iframe><h4>Saving our results</h4><p>Time to apply our functions. We build a dictionary of unsubscribe links, turn it into a dataframe and export it to CSV.</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/d10212ee50d454df14512c6a55dbc8d2/href">https://medium.com/media/d10212ee50d454df14512c6a55dbc8d2/href</a></iframe><p>That’s it! We created a fast and simple e-mail service unsubscribe system. And like I promised, we used no more than 30 lines of code.</p><h3>Results</h3><p>Open the CSV with your favorite software. Now you can easily see who is sending you the most emails, and if you think you had enough, you have a handy unsubscribe link at hand. The names will be a bit messy, but it should be possible to infer the identity of the service and decide whether you want to you keep your subscription or not.</p><p>It shouldn’t take long to do this. I noticed that a few services were responsible for most of my subscription-based emails. In fact, although I found 1859 different services, 25 of them were responsible for more than 50% of the emails.</p><p>The script will not be able to catch services who didn’t fill out the ‘List-Unsubscribe’ field. However, like I said, I was still able to find several hundreds services.</p><p>That’s it! It’s far from perfect, I know. But it’s free, it’s secure, and it’s completely yours.</p><h3>Thank you for reading!</h3><p>You can find the script at my <a href="https://github.com/vlad-ds/mail-unsubscribe">GitHub</a>.</p><h3>Next Steps</h3><p>The method we have used to extract emails is limited. Check out the <a href="https://docs.python.org/2/library/mailbox.html">mailbox</a> library for a more sophisticated approach.</p><p>Moreover, you could use <a href="https://www.crummy.com/software/BeautifulSoup/bs4/doc/">Beautiful Soup</a> to find unsubscribe links that are in the body of the mail rather than in the Unsubscribe field.</p><h4>Disclaimer</h4><p>Some of the links you will gather with this script might be <strong>unsafe</strong>. Proceed with <strong>judgment</strong> and <strong>caution</strong>! Only follow links to services which you recognize. I am not responsible for any unintended consequences.</p><h4>Configuring the MAILTO handle</h4><p>The MAILTO address will only work if it has a handle with your email. To configure this in Google Chrome, go to Settings. In the Advanced Settings section, select Site Settings. Then go to Handlers and make sure that the option to allow sites to become handlers is enabled. If you change this, you will need to restart your browser.</p><p>Now open your Gmail page. On the right of the URL bar, you should see an icon that looks like two joined rhombuses. Click there and allow Google to open email links. Now your MAILTO addresses should work!</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=9aefec644af2" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Get Your Spotify Streaming History With Python]]></title>
            <link>https://medium.com/data-science/get-your-spotify-streaming-history-with-python-d5a208bbcbd3?source=rss-abd2f990eeab------2</link>
            <guid isPermaLink="false">https://medium.com/p/d5a208bbcbd3</guid>
            <category><![CDATA[spotify]]></category>
            <category><![CDATA[music]]></category>
            <category><![CDATA[data-mining]]></category>
            <category><![CDATA[api]]></category>
            <category><![CDATA[python]]></category>
            <dc:creator><![CDATA[Vlad Gheorghe]]></dc:creator>
            <pubDate>Tue, 04 Feb 2020 21:46:42 GMT</pubDate>
            <atom:updated>2020-02-05T07:15:33.576Z</atom:updated>
            <content:encoded><![CDATA[<h4>With delicious song features on top.</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/465/1*zh18qj6xD1FLTE-WuntFAw.png" /><figcaption>It ain’t the same without those features. <a href="https://commons.wikimedia.org/wiki/File:Foodiesfeed.com_pouring-honey-on-pancakes-with-walnuts.jpg">Source</a></figcaption></figure><p><em>This is my first Medium story! Any feedback is appreciated.</em></p><p>If you’re a dedicated Spotifyer like myself, there are lots of things you could learn by looking at your streaming data. Do you tend to listen to sad songs in winter? What happened to your music preferences when you fell in love?</p><p>Luckily, Spotify allows you to request a download of all your streaming history<em>. </em>In this tutorial, I will show you how to extract this data, flavor it with delicious song features, and organize it into a handy <strong>CSV file</strong> that you can analyse with your favorite tools.</p><p>But there’s more. By the time we get there, you will also gain a basic understanding of how the <strong>Spotify API</strong> works, how to complete the <strong>Authorization Code Flow</strong>, and how you can build your own <strong>Spotify app. </strong>Read on!</p><p>But if you’re in a hurry, the full code is available at my <a href="https://github.com/vlad-ds/spoty-records"><strong>GitHub</strong></a><strong>.</strong></p><h4>Features, features</h4><p>Spotify’s <em>audio features</em> are complex metrics that aim to describe a track’s personality and general impression on the listener. Here is a brief description of each:</p><p><strong><em>acousticness</em></strong><em> — how acoustic <br></em><strong><em>danceability</em></strong><em> — self-explanatory<br></em><strong><em>energy</em></strong><em> — how &#39;fast, loud an noisy&#39;<br></em><strong><em>instrumentalness</em></strong><em> — the less vocals, the higher<br></em><strong><em>liveness</em></strong><em> — whether there is audience in the recording<br></em><strong><em>loudness</em></strong><em> — self-explanatory<br></em><strong><em>speechiness</em></strong><em> — the more spoken words, the higher<br></em><strong><em>valence</em></strong><em> — whether the track sounds happy or sad<br></em><strong><em>tempo</em></strong><em> — the bpm</em></p><p>Spotify also measures the duration, key, mode and time signature of each track. You can read more about features in Spotify’s <a href="https://developer.spotify.com/documentation/web-api/reference/tracks/get-audio-features/">documentation</a>.</p><h4>What we need to do</h4><p>First, we get our streaming data from Spotify. Since the features are not included, we request them from the Spotify API. Finally, we export the data to our favorite format.</p><h4>Requirements</h4><p>The main requirement for this task is the <a href="https://github.com/plamere/spotipy/commits?author=stephanebruckert"><strong>Spotipy</strong></a> library. We will also use the <strong>Requests</strong> module for addressing the API. Though not essential, I’ve also included <strong>Pandas</strong> because it makes it very easy to save and load tabular data.</p><p>Make sure you install the necessary dependencies:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/a553c28fa570b7361d900ddcb01c0e1a/href">https://medium.com/media/a553c28fa570b7361d900ddcb01c0e1a/href</a></iframe><h3>The action</h3><h4>Getting the data</h4><p>Access your Spotify account dashboard at <a href="https://www.spotify.com/">https://www.spotify.com/</a>. In the privacy settings, you’ll find the option to request your data. This requires some patience. Spotify says it takes up to thirty days, but it’s usually much faster. In my case I waited three days.</p><p>Eventually you will get an email with your Spotify data in a .zip file. Extract the <em>MyData</em> folder and copy it in your working folder.</p><h4>Acquiring the streamings</h4><p>There are several files in our folder. Those that interest us look like this: <em>StreamingHistory0.json</em>. You might have one file or more, depending on the size of your streaming history. Let’s open the file. My first song looks like this:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/7d01efadda44b9487705b3124448b7e3/href">https://medium.com/media/7d01efadda44b9487705b3124448b7e3/href</a></iframe><p>Not a bad choice, right?</p><p>Let’s write a Python function that will collect all <em>StreamingHistory</em> files, extract the JSON objects and convert them into Python dictionaries.</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/d0d2150f238d28081b8394ffbd44825c/href">https://medium.com/media/d0d2150f238d28081b8394ffbd44825c/href</a></iframe><p>That’s it. Now we have a timestamped list of all the tracks in our history.</p><h4>Becoming a Spotify Developer</h4><p>Spotify does not include song features in its data download. We’ll have to request them via the Spotify API.</p><p>Access to the API is free, but we’ll need to register a Spotify app. No worries: it only takes a few minutes. Just sign up <a href="https://developer.spotify.com/">here</a>.</p><p>Congratulations: you are officially a Spotify developer!</p><p>Go to your new <a href="https://developer.spotify.com/dashboard/applications">developer dashboard</a> and click on ‘Create an App’. Don’t worry about the details. Spotify will allow you to create dummy apps as long as you promise not to monetize them. But you should avoid using ‘Spotify’ in the name, or it might get blocked.</p><h4>Authorization Code Flow</h4><p>Nobody could explain it this simply to me, so here goes. An app can access the Spotify API, but only if it gets permission from at least one user. So we’ll use the app to ask ourselves permission to access our user<em> </em>data.</p><p>We need to provide a ‘redirect link’ that we’ll use to collect the user’s permission. From your app’s panel in the developer dashboard, click on ‘Edit Settings’ and add a link under <strong>Redirect URIs. </strong>This doesn’t have to be a real link: if you don’t have a website, you can simply use <a href="http://localhost:7777/callback">http://localhost:7777/callback</a>.</p><p>You will also need your app’s <strong>Client ID</strong> and <strong>Client Secret</strong>. You’ll find them in the app panel under your app’s name. Now you have all you need to access the Spotify API!</p><h4>Accessing the Spotify API</h4><p><strong>Spotipy</strong> to the rescue. Insert the variables you just collected in these fields:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/ee9272345fa70e8fb491a77e21abfe54/href">https://medium.com/media/ee9272345fa70e8fb491a77e21abfe54/href</a></iframe><p>This function packages a <strong>request</strong> that goes from your app (identified through the <strong>Client Id</strong> and the <strong>Client Secret</strong>) to the user (identified by the Spotify <strong>username</strong>).</p><p>The request has a <strong>scope</strong>, which defines which permissions you’re going to ask. You can learn more about scopes <a href="https://developer.spotify.com/documentation/general/guides/scopes/">here</a>.</p><p>Finally, you’ll need to provide a <strong>Redirect URI</strong>. This must correspond to the one you white-listed in your app’s settings (see previous section).</p><p>The function returns a <strong>token</strong>, which is basically a string that we’ll use to assure the Spotify API that we have user authorization.</p><p>Once you run the function with the right parameters, it will open an authorization panel in your web browser. Follow the link, log with your Spotify credentials and you should see something like this:</p><figure><img alt="The Spotify authorization panel." src="https://cdn-images-1.medium.com/max/351/1*4uMfhpJfbiT-vO0MCOZvtQ.png" /></figure><p>Now you can finally authorize your app. Once you click on Agree, you will be taken to the Redirect URI, which may well be a nonexistent page. Just copy the address and paste it in your Python console.</p><p>That’s it. If you print your <em>token</em> variable in the console, you should see something like this:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/708c43c1a6533093d055c15a920121f2/href">https://medium.com/media/708c43c1a6533093d055c15a920121f2/href</a></iframe><h4>Access token and refresh token</h4><p>But there’s more. If you shut down your code and run it again, you won’t have to provide authorization, despite the <em>token </em>variable having been lost to memory. Spotipy seems to remember your token. What is happening here?</p><p>If you go to your working folder and enable hidden files visualization, you will see that Spotipy has created a new file named <em>.cache-your-username.</em></p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/18e0435efed5e9bcbb8272e93af0764a/href">https://medium.com/media/18e0435efed5e9bcbb8272e93af0764a/href</a></iframe><p>As you can see, your <strong>access token</strong> has an expiration period, typically of one hour. But Spotify also provides you with a <strong>refresh token</strong>. When the original token expires, your app can use the refresh token to request a new one.</p><p>Hence, it’s important that you call the <em>prompt_for_user_token </em>function to load the token every time you run your script. If it finds a cache file, it will use it. If you move or delete the cache file, your tokens will be lost and the user will have to authorize you again.</p><h4><strong>Getting IDs</strong></h4><p>The Spotify data download does not provide us with tracks’ <strong>IDs.</strong> We need those IDs to get the features.</p><p>We can obtain the IDs by using the API to search the name of our track, taking the first result and extracting the ID. Spotify shows how to build such requests in the <a href="https://developer.spotify.com/documentation/web-api/reference/search/search/">documentation</a>.</p><p>I wanted to use the <strong>Requests</strong> library to perform this in Python. Since I’m not yet familiar with it, I used the script at <a href="https://curl.trillworks.com/">https://curl.trillworks.com/</a> to convert the curl command into Python Requests code. Here is Spotify’s example of a curl command:</p><pre>curl -X GET &quot;https://api.spotify.com/v1/search?q=tania%20bowra&amp;type=artist&quot; -H &quot;Authorization: Bearer {your access token}&quot;</pre><p>And here is the function I wrote with the help of <a href="https://github.com/NickCarneiro/curlconverter">curlconverter</a>:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/f727f5772b03d636497d431b40a27407/href">https://medium.com/media/f727f5772b03d636497d431b40a27407/href</a></iframe><p>Let’s test our function:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/55661e3daee171b522e3e7e8c86a3821/href">https://medium.com/media/55661e3daee171b522e3e7e8c86a3821/href</a></iframe><p>Nice. <a href="https://open.spotify.com/album/6QaVfG1pHYl1z15ZxkvVDW?highlight=spotify:track:25yQPHgC35WNnnOUqFhgVR">Paste that ID in Open Spotify</a> and get a load of classic.</p><h4><strong>Getting the features</strong></h4><p>Now that we have our IDs, it’s a breeze to get the features from the API.</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/a7057438eea7bb17338ab7eb384cb32b/href">https://medium.com/media/a7057438eea7bb17338ab7eb384cb32b/href</a></iframe><p>Let’s test it on our track:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/707d626dd35cbdec5f8b2bce6fb7bf8c/href">https://medium.com/media/707d626dd35cbdec5f8b2bce6fb7bf8c/href</a></iframe><h4>Wrapping it up</h4><p>Wonderful! Now we have all we need to build our streaming history <strong>dataframe</strong>. The easiest way is to create a list of dictionaries:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/cbbb825b60019607d109bf1d83381e74/href">https://medium.com/media/cbbb825b60019607d109bf1d83381e74/href</a></iframe><p>(If you have thousands of songs in your history, getting the data from the API could take considerable time. I suggest you first test your code on a sample.)</p><p><em>Voilà.</em> We have a nice stack of streamings, and with features to boot. Now we can use Pandas to turn our list of dicts into a dataframe and export it to CSV.</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/49d5b7f527c8e424a5b6582879d2edb1/href">https://medium.com/media/49d5b7f527c8e424a5b6582879d2edb1/href</a></iframe><h3><strong>We’re done!</strong></h3><p>If you head over to my <a href="https://github.com/vlad-ds/spoty-records"><strong>GitHub</strong></a>, you’ll see my code is a bit different. When you repeat API requests, you might get responses that were denied in the first run. Also, in the future you might want to rerun the code with a new batch of streamings. But API requests are slow, so I’ve added functions that save the IDs and features that you already collected, allowing you to make new requests without repeating old ones.</p><h3>What’s next?</h3><p>Once you have your streaming history with all the features, you can unsheathe your <strong>Pandas/Matplotlib</strong> skills to analyse and plot them. You could even <a href="https://towardsdatascience.com/a-music-taste-analysis-using-spotify-api-and-python-e52d186db5fc">apply machine learning</a> to answer some interesting questions.</p><p>If you have a friend and partner who’s into Spotify, you can use this script to gift them their history. Just enter their username in your code, send them the authorization link, and make sure they send you the Redirect URI.</p><p>In the follow-up, I’ll explore how we can analyse the data we just collected. Meanwhile, you can check <a href="https://towardsdatascience.com/is-my-spotify-music-boring-an-analysis-involving-music-data-and-machine-learning-47550ae931de">other cool articles</a> on the subject.</p><p><em>That’s the end of my first Medium story. Thanks for reading!</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=d5a208bbcbd3" width="1" height="1" alt=""><hr><p><a href="https://medium.com/data-science/get-your-spotify-streaming-history-with-python-d5a208bbcbd3">Get Your Spotify Streaming History With Python</a> was originally published in <a href="https://medium.com/data-science">TDS Archive</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
    </channel>
</rss>