The next cohort starts January 13th, 2025. More info at DTC.

• Register before the course starts using this link.
• Join the course Telegram channel with announcements.
• Don’t forget to register in DataTalks.Club's Slack and join the channel.

To get the most out of this course, you should have:

• Basic coding experience
• Familiarity with SQL
• Experience with Python (helpful but not required)

No prior data engineering experience is necessary. See Readme on GitHub.

Yes, even if you don't register, you're still eligible to submit the homework.

Be aware, however, that there will be deadlines for turning in homeworks and the final projects. So don't leave everything for the last minute.

You don't need a confirmation email. You're accepted. You can start learning and submitting homework without registering. Registration was just to gauge interest before the start date.

Start by installing and setting up all the dependencies and requirements:

• Google Cloud account
• Google Cloud SDK
• Python 3 (installed with Anaconda)
• Terraform
• Git

Look over the prerequisites and syllabus to see if you are comfortable with these subjects.

There are multiple Zoomcamps in a year, as of 2025. More info at DTC Article.

They are five separate courses, estimated to be during these months:

• Data-Engineering: Jan - Apr
• Stock Market Analytics: Apr - May
• MLOps: May - Aug
• LLM: June - Sep
• Machine Learning: Sep - Jan

There's only one Data-Engineering Zoomcamp “live” cohort per year for the certification, similar to the other Zoomcamps. They follow pretty much the same schedule for each cohort. For Data-Engineering, it is generally from Jan-Apr of the year.

If you’re not interested in the Certificate, you can take any Zoomcamp at any time, at your own pace, out of sync with any “live” cohort.

For the 2025 edition, we are using Kestra (see Demo) instead of MageAI (Module 2). Look out for new videos. See Playlist.

For the 2024 edition, we used Mage AI instead of Prefect and re-recorded the Terraform videos. For 2023, we used Prefect instead of Airflow. See playlists on YouTube and the cohorts folder in the GitHub repo.

Yes, we will keep all the materials available, so you can follow the course at your own pace after it finishes.

You can also continue reviewing the homeworks and prepare for the next cohort. You can also start working on your final capstone project.

Yes, the Slack channel remains open and you can ask questions there. However, always search the channel first and check the FAQ, as most likely your questions are already answered here.

You can also tag the bot @ZoomcampQABot to help you conduct the search, but don’t rely on its answers 100%.

All the main videos are stored in the "DATA ENGINEERING ZOOMCAMP" main playlist. The GitHub repository is updated to include each video with a thumbnail, linking directly to the relevant playlist.

Refer to the Main Playlist for the core content, and then check specific year playlists for additional videos such as office hours.

• Data Engineering Zoomcamp
• Data Engineering Zoomcamp 2022
• Data Engineering Zoomcamp 2023
• Data Engineering Bootcamp 2024
• Data Engineering Bootcamp 2025
• DE Zoomcamp 2025 (Module 2 Kestra)

It depends on your background and previous experience with modules. It is expected to require about 5 - 15 hours per week.

You can also calculate it yourself using this data and then update this answer.

Yes! Every "Office Hours" will be recorded and available a few minutes after the live session is over; so you can view (or rewatch) whenever you want.

The display name listed on the leaderboard is an auto-generated randomized name. You can edit it to be a nickname or your real name if you prefer. Your entry on the Leaderboard is the one highlighted in light green.

The Certificate name should be your actual name that you want to appear on your certificate after completing the course.

The "Display on Leaderboard" option indicates whether you want your name to be listed on the course leaderboard.

No, as long as you complete the peer-reviewed capstone projects on time, you can receive the certificate. You do not need to do the homeworks if you join late, for example.

No, you can only get a certificate if you finish the course with a “live” cohort. We don't award certificates for the self-paced mode. The reason is you need to peer-review capstone(s) after submitting a project. You can only peer-review projects at the time the course is running.

2025 deadlines will be announced on the course website and in Google Calendar.

You can find the 2024 deadlines here: 2024 Deadlines Spreadsheet.

Also, take note of announcements from @Au-Tomator for any extensions or other news. The form may also show the updated deadline if the instructor(s) have updated it.

No, late submissions are not allowed. However, if the form is still open after the due date, you can still submit the homework. Confirm your submission by checking the date-timestamp on the Course page. Ensure you are logged in.

Answer: In short, it’s your repository on GitHub, GitLab, Bitbucket, etc.

In long, your repository or any other location where you have your code, and a reasonable person would look at it and think, yes, you went through the week and exercises. Think of it like a portfolio you could present to an employer.

After you submit your homework, it will be graded based on the number of questions in that particular assignment. You can see the number of points you have earned at the top of the homework page. Additionally, in the leaderboard, you will find the sum of all points you've earned: points for Homeworks, FAQs, and Learning in Public.

Point System Overview:

• Homework: Points vary by assignment based on the number of questions.
• FAQ Contribution: You get a maximum of 1 point for contributing to the FAQ in the respective week.
• Learning in Public: For each learning in public link, you earn one point. You can achieve a maximum of 7 points.

Check this video for more details.

When you set up your account, you are automatically assigned a random name, such as "Lucid Elbakyan." If you want to see what your display name is, follow these steps:

• Go to your profile.
  • 2025: https://courses.datatalks.club/de-zoomcamp-2025/enrollment
  • 2024: https://courses.datatalks.club/de-zoomcamp-2024/enrollment
• Log in.
• Your display name is shown. You can also change it if you wish.
• Ensure your certificate name is correct, as this name will later be printed on your certificate.

Yes, for simplicity and stability when troubleshooting against recorded videos.

But Python 3.10 and 3.11 should work fine.

You can set it up on your laptop or PC if you prefer to work locally. However, Windows users might face some challenges.

If you prefer to work on the local machine, you can start with the Week 1 Introduction to Docker.

Alternatively, if you prefer to set up a virtual machine, consider the following:

• Using GitHub Codespaces
• Setting up the environment on a cloud VM: Refer to this video for guidance.

Working on a virtual machine is beneficial if you have different devices for home and office, allowing you to work virtually anywhere.

GitHub Codespaces offers you computing Linux resources with many pre-installed tools (Docker, Docker Compose, Python).

You can also open any GitHub repository in a GitHub Codespace.

It's up to you which platform and environment you use for the course.

GitHub Codespaces or GCP VM are just possible options, but you can do the entire course from your laptop.

Choose the approach that aligns the most with your idea for the end project.

One should suffice; however, BigQuery, which is part of GCP, will be used, so learning that is probably a better option. Alternatively, you can set up a local environment for most of this course.

This issue occurs when attempting to connect to a GCP VM using VSCode on a Windows machine. You can resolve it by changing a registry value in the registry editor.

Open the Run command window:

• Use the shortcut keys Windows + R, or
• Right-click "Start" and click "Run".

Open the Registry Editor:

• Type regedit in the Run command window, then press Enter.

Change the registry value:

• Navigate to HKEY_CURRENT_USER\Software\Microsoft\Command Processor.
• Change the "Autorun" value from "if exists" to a blank.

Alternatively, you can delete the saved fingerprint within the known_hosts file:

In Windows, locate the file at C:\Users\<your_user_name>\.ssh\known_hosts and remove the entry for the server.

For uniformity.

You can use other cloud platforms since you get every service provided by GCP in Azure and AWS. You’re not restricted to GCP and can use other platforms like AWS if you’re more comfortable.

Because everyone usually has a Google account, GCP offers a free trial period with $300 in credits for new users. Additionally, we are working with BigQuery, which is part of GCP.

Note that to sign up for a free GCP account, you must have a valid credit card.

It's not mandatory. You can take advantage of their free trial.

You can do most of the course without a cloud. Almost everything we use (excluding BigQuery) can be run locally. We won’t be able to provide guidelines for some things, but most of the materials are runnable without GCP.

For everything in the course, there’s a local alternative. You could even do the whole course locally. Note that Homework 3 requires BigQuery.

Google Cloud Platform (GCP) provides two free trial options: the Free Trial with $300 credit and the Sandbox. Users can switch between these options by managing billing details.

However, completing the course solely using the GCP Sandbox option is not feasible due to its limited features. The Sandbox lacks some services required for the course, such as VMs, GCS Buckets, and other paid services that are integral to the curriculum.

The course will eventually require utilizing the following:

• VMs and GCS Buckets: These resources are not fully available in the Sandbox.
• BigQuery: A key component of GCP, and the Sandbox may not support all necessary functionalities.

Therefore, it is recommended to use the GCP Free Trial with billing details to access all needed features and ensure a smooth learning experience.

Yes, you can. Just remember to adapt all the information from the videos to AWS. Additionally, the final capstone will be evaluated based on these tasks:

• Create a data pipeline
• Develop a visualization

Consider that when seeking help, you might need to rely on fellow coursemates who use AWS, which could be fewer compared to those using GCP.

Also, see "Is it possible to use x tool instead of the one tool you use?"

We will probably have some calls during the Capstone period to clear some questions, but it will be announced in advance if that happens.

See Google Calendar

No, but we moved the 2022 stuff to the cohort 2022 folder on GitHub here.

Yes, you can use any tool you want for your project.

Yes, this applies if you want to use Airflow or Prefect instead of Mage, AWS or Snowflake instead of GCP products, or Tableau instead of Metabase or Google Data Studio.

The course covers two alternative data stacks: one using GCP and one using local installation of everything. You can use one of them or use your tool of choice.

Considerations:

• We can’t support you if you choose to use a different stack.
• You would need to explain the different choices of tools for the peer review of your capstone project.

• Star the repository.
• Share it with friends if you find it useful.
• Create a pull request (PR) if you can improve the text or structure of the repository.
• Update this FAQ.

Yes! Linux is ideal but technically it should not matter. Students in the 2024 cohort used all 3 OSes successfully.

Later modules (module-05 & RisingWave workshop) use shell scripts in *.sh files. Most Windows users not using WSL will encounter issues and may not be able to continue, even in Git Bash or MINGW64. It is recommended to set up a WSL environment from the start.

Yes to both! Check out this document: Awesome Data Engineering Resources

You will have two attempts for a project.

• If the first project deadline is over and you’re late, or you submit the project and fail the first attempt, you have another chance to submit the project with the second attempt.

First Steps:

• Attempt to solve the issue independently. Familiarize yourself with documentation as a crucial skill for problem-solving.
• Use shortcuts like [ctrl+f] to search within documents and browsers.
• Analyze the error message for descriptions, instructions, and possible solutions.
• Restart your application, server, or computer as needed.

Search for Solutions:

• Use search engines like Google, ChatGPT, or Bing AI to research the issue. It is rare to encounter a unique problem.
• Form your search queries using: <technology> <problem statement>. E.g., pgcli error column c.relhasoids does not exist.
• Consult the technology’s official documentation for guidance.

Uninstallation and Reinstallation:

• Uninstall and then reinstall the application if needed, including a system restart.
• Note that reinstalling without prior uninstallation might not resolve the issue.

Seeking Help:

• Post questions on platforms like StackOverflow. Ensure your question adheres to guidelines: how-to-ask.
• Consider asking experts or colleagues in the future.

Community Resources:

• Check Slack channels for pinned messages and use its search function.
• Refer to this FAQ using search [ctrl+f] or utilize the @ZoomcampQABot for assistance.

When Asking for Help:

• Provide detailed information: coding environment, OS, commands, videos followed, etc.
• Share errors received, with specifics, including line numbers and actions taken.
• Avoid screenshots; paste code or errors directly. Use ``` for code formatting.
• Maintain thread consistency; respond in the same thread instead of creating multiple ones.

Re-evaluation:

• If the issue recurs, create a new post detailing changes in the environment.
• Communicate additional troubleshooting steps in the same thread.
• Occasionally take a break to gain a fresh perspective on the problem.

Documentation Contribution:

• If your problem solution is not listed, consider adding it to the FAQ to assist others.

When the troubleshooting guide does not help resolve your issue and you need another pair of eyes, include as much information as possible when asking a question:

• What are you coding on? What operating system are you using?
• What command did you run, and which video or tutorial did you follow?
• What error did you get? Does it have a line number pointing to the problematic code, and have you checked it for typos?
• What have you tried that did not work? This is crucial because, without it, helpers might suggest steps mentioned in the error log first. Or just refer to this FAQ document.

After you create a GitHub account, clone the course repo to your local machine using the process outlined in this video:

Git for Everybody: How to Clone a Repository from GitHub.

Having this local repository on your computer will make it easy to access the instructors’ code and make pull requests if you want to add your own notes or make changes to the course content.

You will probably also create your own repositories to host your notes and versions of files. Here is a great tutorial that shows you how to do this:

How to Create a Git Repository.

Remember to ignore large databases, .csv, and .gz files, and other files that should not be saved to a repository. Use .gitignore for this:

.gitignore file.

Important:

NEVER store passwords or keys in a git repo (even if the repo is set to private). Put files containing sensitive information (.env, secret.json, etc.) in your .gitignore.

This is also a great resource: Dangit, Git!?!

Error:

Makefile:2: *** missing separator.  Stop.

Solution:

Tabs in documents should be converted to Tab instead of spaces. Follow this stack.

If you’re running Linux on Windows Subsystem for Linux (WSL) 2, you can open HTML files from the guest (Linux) with any Internet Browser installed on the host (Windows). Just install wslu and open the page using wslview:

wslview index.html

You can customize which browser to use by setting the BROWSER environment variable first. For example:

export BROWSER='/mnt/c/Program Files/Firefox/firefox.exe'

This tutorial shows you how to set up the Chrome Remote Desktop service on a Debian Linux virtual machine (VM) instance on Compute Engine. Chrome Remote Desktop allows you to remotely access applications with a graphical user interface.

There'll be an announcement in Telegram and the course channel for:

• Checking that your full name is displayed correctly on the Certificate (see Editing course profile on the Course Management webpage).
• Notifying when the grading is completed.

You will find it in your course profile (you need to be logged it).

For 2025 the link to the course profile is this:

https://courses.datatalks.club/de-zoomcamp-2025/enrollment

For other editions, change "2025" to your edition.

After the second announcement, follow instructions in certificates.md on how to generate the Certificate document yourself.

When attempting to download the 2021 data from the TLC website, you may encounter the following error:

ERROR 403: Forbidden

We have a backup, so use it instead: nyc-tlc-data

So the link should be yellow_tripdata_2021-01.csv.gz.

Note: Make sure to unzip the "gz" file (no, the "unzip" command won’t work for this).

In this video, the data file is stored as output.csv. If the file extension is csv.gz instead of csv, it won't store correctly.

To handle this:

1. Replace csv_name = "output.csv" with the file name extracted from the URL. For example, for the yellow taxi data, use:

   url = "https://github.com/DataTalksClub/nyc-tlc-data/releases/download/yellow/yellow_tripdata_2021-01.csv.gz"
   csv_name = url.split("/")[-1]
   

2. When you use csv_name with pandas.read_csv, it will work correctly because pandas.read_csv can directly read files with the csv.gz extension.

Example:

import pandas as pd

url = "https://github.com/DataTalksClub/nyc-tlc-data/releases/download/yellow/yellow_tripdata_2021-01.csv.gz"
csv_name = url.split("/")[-1]

data = pd.read_csv(csv_name)

Yellow Trips: Data Dictionary

Green Trips: Data Dictionary - LPEP Trip Records May 1, 2018

You can unzip the downloaded parquet file from the command line. The result is a CSV file which can be imported with pandas using pd.read_csv() as shown in the videos.

gunzip green_tripdata_2019-09.csv.gz

Solution for Using Parquet Files Directly in Python Script ingest_data.py

1. In the def main(params), add this line:

   parquet_name = 'output.parquet'
   

2. Edit the code which downloads the files:

   os.system(f"wget {url} -O {parquet_name}")
   

3. Convert the downloaded .parquet file to CSV and rename it to csv_name to keep it relevant to the rest of the code:

   df = pd.read_parquet(parquet_name)
   df.to_csv(csv_name, index=False)
   

If you encounter the error "wget is not recognized as an internal or external command," wget needs to be installed.

This error may also cause messages like "No such file or directory: 'output.csv.gz'."

Installation Instructions:

• On Ubuntu:

  sudo apt-get install wget
  

• On macOS:

  Use Homebrew:

  brew install wget
  

• On Windows:

  Use Chocolatey:

  choco install wget
  

  Alternatively, download a binary from GnuWin32 and place it in a location that is in your PATH (e.g., C:/tools/).

Alternative Windows Installation:

1. Download the latest wget binary for Windows from eternallybored.
2. If you downloaded the zip, extract all files (use 7-zip if the built-in utility gives an error).
3. Rename the file wget64.exe to wget.exe if necessary.
4. Move wget.exe to your Git\mingw64\bin\ directory.

Python Alternative:

• Use the Python wget library:

  First, install using pip:

  pip install wget
  

• Use it with Python:

  python -m wget
  

You can also paste the file URL into your web browser to download normally, then move the file to your working directory.

Additional Recommendation:

Consider using the Python library requests for loading gz files.

Firstly, make sure that you add ! before wget if you’re running your command in a Jupyter Notebook or CLI. Then, you can check one of these two things (from CLI):

• Using the Python library wget installed with pip:

  python -m wget <url>
  

• Use the usual command and add --no-check-certificate at the end:

  !wget <website_url> --no-check-certificate
  

For those who wish to use the backslash as an escape character in Git Bash for Windows, type the following in the terminal:

bash.escapeChar=\

(Note: There is no need to include this in your .bashrc file.)

Instruction on how to store secrets that will be available in GitHub Codespaces. See Managing your account-specific secrets for GitHub Codespaces - GitHub Docs.

With the default instructions, running pgadmin in Docker may result in a blank screen after logging into the pgadmin console. To resolve this, add the following two environment variables to your pgadmin configuration to allow it to work with Codespaces’ reverse proxy:

PGADMIN_CONFIG_PROXY_X_HOST_COUNT: 1
PGADMIN_CONFIG_PROXY_X_PREFIX_COUNT: 1

Make sure you're able to start the Docker daemon. Check the issue immediately as described below:

• Ensure the Docker daemon is running.

• Update WSL in PowerShell with the following command:

  wsl --update
  

If you get this error:

docker: error during connect: In the default daemon configuration on Windows, the docker client must be run with elevated privileges to connect.: Post "http://%2F%2F.%2Fpipe%2Fdocker_engine/v1.24/containers/create": open //./pipe/docker_engine: The system cannot find the file specified.
See 'docker run --help'.

To resolve it on Windows, follow these guidelines based on your version:

Windows 10 Pro / 11 Pro Users:

• Ensure Hyper-V is enabled, as Docker can use it as a backend.
• Follow the Enable Hyper-V Option on Windows 10 / 11 tutorial.

Windows 10 Home / 11 Home Users:

• The 'Home' version doesn't support Hyper-V, so use WSL2 (Windows Subsystem for Linux).
• Refer to install WSL on Windows 11 for detailed instructions.

If you encounter the "WslRegisterDistribution failed with error: 0x800701bc" error:

• Update the WSL2 Linux Kernel by following the guidelines at GitHub: WSL Issue 5393.

Whenever a docker pull is performed (either manually or by docker-compose up), it attempts to fetch the given image name from a repository. If the repository is public, the fetch and download occur without any issues.

For instance:

docker pull postgres:13
docker pull dpage/pgadmin4

Be Advised: The Docker images we'll be using throughout the Data Engineering Zoomcamp are all public, unless otherwise specified. This means you are not required to perform a docker login to fetch them.

If you encounter the message:

docker login': denied: requested access to the resource is denied.

This is likely due to a typo in your image name. For instance:

$ docker pull dbpage/pgadmin4

This command will throw an exception:

Error response from daemon: pull access denied for dbpage/pgadmin4, repository does not exist or may require 'docker login': denied: requested access to the resource is denied

This occurs because the actual image name is dpage/pgadmin4, not dbpage/pgadmin4.

How to fix it:

$ docker pull dpage/pgadmin4

Extra Notes: In some professional environments, the Docker image may be in a private repository that your DockerHub username has access to. In this case, you must:

1. Execute:

   $ docker login
   

2. Enter your username and password.
3. Then perform the docker pull against that private repository.

When attempting to run a Docker command similar to the one below:

docker run -it \
  -e POSTGRES_USER="root" \
  -e POSTGRES_PASSWORD="root" \
  -e POSTGRES_DB="ny_taxi" \
  -v $(pwd)/ny_taxi_postgres_data:/var/lib/postgresql/data \
  -p 5432:5432 \
  postgres:13

You encounter the error message:

docker: Error response from daemon: error while creating mount source path '/path/to/ny_taxi_postgres_data': chown /path/to/ny_taxi_postgres_data: permission denied.

Solution

1. Stop Rancher Desktop:
   If you are using Rancher Desktop and face this issue, stop Rancher Desktop to resolve compatibility problems.

2. Install Docker Desktop:
   Install Docker Desktop, ensuring that it is properly configured and has the required permissions.

3. Retry Docker Command:
   Run the Docker command again after switching to Docker Desktop. This step resolves compatibility issues on some systems.

Note: The issue occurred because Rancher Desktop was in use. Switching to Docker Desktop resolves compatibility problems and allows for the successful creation of PostgreSQL containers with mounted volumes for the New York Taxi Database on macOS M1.

When a PostgreSQL Docker container is created, it may create a folder on the local machine to mount to a volume inside the container. This folder is often owned by user 999 and has read and write protection, preventing deletion by conventional means such as dragging it to the trash.

If you encounter an access error or need to delete the folder, you can use the following command:

sudo rm -r -f docker_test/

• rm : Command to remove files or directories.
• -r : Recursively remove directories and their contents.
• -f : Forcefully remove files/directories without prompting.
• docker_test/ : The folder to be deleted.

Ensure you are running the latest version of Docker for Windows. Download the updated version from Docker's official site. If the upgrade option in the menu doesn't work, uninstall and reinstall with the latest version.

If Docker is stuck on starting, try switching the containers by right-clicking the docker symbol from the running programs, and switch the containers from Windows to Linux or vice versa.

For Windows 10 / 11 Pro Edition:

• Hyper-V Backend: ensure Hyper-V is enabled by following this tutorial.
• WSL2 Backend: follow the steps detailed in this tutorial.

If you're running a Home Edition, you can still make it work with WSL2 (Windows Subsystem for Linux) by following the tutorial here.

If even after making sure your WSL2 (or Hyper-V) is set up accordingly, Docker remains stuck, you can try the following options:

• Reset to Factory Defaults
• Perform a fresh install.

You may encounter this error:

$ docker run -it ubuntu bash
the input device is not a TTY. If you are using mintty, try prefixing the command with 'winpty'

Solution:

• Use winpty before the Docker command:

  $ winpty docker run -it ubuntu bash
  

• Alternatively, create an alias:

  echo "alias docker='winpty docker'" >> ~/.bashrc
  

  or

  echo "alias docker='winpty docker'" >> ~/.bash_profile
  

Source: Stack Overflow

You may encounter this error:

Retrying (Retry(total=4, connect=None, read=None, redirect=None, status=None)) after connection broken by 'NewConnectionError('<pip._vendor.urllib3.connection.HTTPSConnection object at 0x7efe331cf790>: Failed to establish a new connection: [Errno -3] Temporary failure in name resolution')': /simple/pandas/

Possible solution:

Run the following command:

winpty docker run -it --dns=8.8.8.8 --entrypoint=bash python:3.9

Even after properly running the Docker script, the folder may appear empty in VS Code. For Windows, try the following steps:

Solution 1:

Run the Docker command with the absolute path quoted in the -v parameter:

winpty docker run -it \
  -e POSTGRES_USER="root" \
  -e POSTGRES_PASSWORD="root" \
  -e POSTGRES_DB="ny_taxi" \
  -v "C:\Users\abhin\dataengg\DE_Project_git_connected\DE_OLD\week1_set_up\docker_sql/ny_taxi_postgres_data:/var/lib/postgresql/data" \
  -p 5432:5432 \
  postgres:13

This should resolve the visibility issue in the VS Code ny_taxi folder.

Note: Ensure the correct direction for the slashes: / versus \.

Solution 2:

Another possible solution for Windows is to finish the folder path with a forward slash /:

docker run -it \
  -e POSTGRES_USER="root" \
  -e POSTGRES_PASSWORD="root" \
  -e POSTGRES_DB="ny_taxi" \
  -v /"$(pwd)"/ny_taxi_postgres_data/:/var/lib/postgresql/data/ \
  -p 5432:5432 \
  postgres:13

These steps should help resolve the issue of the ny_taxi_postgres_data folder appearing empty in your Docker setup.

For setting up Docker on macOS, you have two main options:

1. Download from Docker Website:

   • Visit the official Docker website and download the Docker Desktop for Mac as a .dmg file. This method is generally reliable and avoids issues related to licensing changes.

2. Using Homebrew:

   • Be aware that there can be conflicts when installing with Homebrew, especially between Docker Desktop and command-line tools. To avoid issues:

     • Install Docker Desktop first.
     • Then install the command line tools.

   • Commands:

     brew install --cask docker
     

     brew install docker docker-compose
     

   • For more detailed issues related to brew install, refer to this Issue.

For more details, you can check the article on Setting up Docker in macOS.

docker run -it \
  -e POSTGRES_USER="root" \
  -e POSTGRES_PASSWORD="admin" \
  -e POSTGRES_DB="ny_taxi" \
  -v "/mnt/path/to/ny_taxi_postgres_data":"/var/lib/postgresql/data" \
  -p 5432:5432 \
  postgres:13

Error Message:

The files belonging to this database system will be owned by user "postgres". 
The database cluster will be initialized with locale "en_US.utf8". 
The default database encoding has accordingly been set to "UTF8". 
Data page checksums are disabled.
fixing permissions on existing directory /var/lib/postgresql/data ...
initdb: error: could not change permissions of directory "/var/lib/postgresql/data": Operation not permitted

Solution:

1. Create a local Docker volume and map it to the Postgres data directory /var/lib/postgresql/data.

   • The volume name dtc_postgres_volume_local must match in both commands below:

   docker volume create --name dtc_postgres_volume_local -d local
   

2. Run the Docker container using the created volume:

   docker run -it \
     -e POSTGRES_USER="root" \
     -e POSTGRES_PASSWORD="root" \
     -e POSTGRES_DB="ny_taxi" \
     -v dtc_postgres_volume_local:/var/lib/postgresql/data \
     -p 5432:5432 \
     postgres:13
   

3. Verify the command works in Docker Desktop under Volumes. The dtc_postgres_volume_local should be listed, but the folder ny_taxi_postgres_data will be empty as an alternative configuration is used.

Alternate Error:

initdb: error: directory "/var/lib/postgresql/data" exists but is not empty

To resolve this, either remove or empty the directory "/var/lib/postgresql/data", or run initdb.

Mapping volumes on Windows can be tricky. If the approach shown in the course video doesn't work for you, consider the following suggestions:

• Move your data to a directory without spaces. For example, move from C:/Users/Alexey Grigorev/git/... to C:/git/....

• Replace the -v part with one of these options:

  -v /c:/some/path/ny_taxi_postgres_data:/var/lib/postgresql/data
  -v //c:/some/path/ny_taxi_postgres_data:/var/lib/postgresql/data
  -v /c/some/path/ny_taxi_postgres_data:/var/lib/postgresql/data
  -v //c/some/path/ny_taxi_postgres_data:/var/lib/postgresql/data
  --volume //driveletter/path/ny_taxi_postgres_data/:/var/lib/postgresql/data
  

• Add winpty before the whole command:

  winpty docker run -it \
  -e POSTGRES_USER="root" \
  -e POSTGRES_PASSWORD="root" \
  -e POSTGRES_DB="ny_taxi" \
  -v /c:/some/path/ny_taxi_postgres_data:/var/lib/postgresql/data \
  -p 5432:5432 \
  postgres:1
  

• Try adding quotes:

  -v "/c:/some/path/ny_taxi_postgres_data:/var/lib/postgresql/data"
  -v "//c:/some/path/ny_taxi_postgres_data:/var/lib/postgresql/data"
  -v “/c/some/path/ny_taxi_postgres_data:/var/lib/postgresql/data"
  -v "//c/some/path/ny_taxi_postgres_data:/var/lib/postgresql/data"
  -v "c:\some\path\ny_taxi_postgres_data":/var/lib/postgresql/data
  

• Note: If Windows automatically creates a folder called ny_taxi_postgres_data;C, it suggests a problem with volume mapping. Try deleting both folders and replacing the -v part with other options. Using //c/ instead of /c/ might work, as it creates the correct folder ny_taxi_postgres_data.

• A possible solution is using "$(pwd)"/ny_taxi_postgres_data:/var/lib/postgresql/data and pay attention to the placement of quotes.

• If none of these work, use a volume name instead of the path:

  -v ny_taxi_postgres_data:/var/lib/postgresql/data
  

• For Mac, you can wrap $(pwd) with quotes:

  docker run -it \
  -e POSTGRES_USER="root" \
  -e POSTGRES_PASSWORD="root" \
  -e POSTGRES_DB="ny_taxi" \
  -v "$(pwd)"/ny_taxi_postgres_data:/var/lib/postgresql/data \
  -p 5432:5432 \
  postgres:13
  

Source: StackOverflow

Change the mounting path. Replace it with one of the following:

-v /e/zoomcamp/...:/var/lib/postgresql/data

Or

-v /c:/.../ny_taxi_postgres_data:/var/lib/postgresql/data

(Note: Include a leading slash in front of c:)

You may get this error:

error while creating buildmount source path '/run/desktop/mnt/host/c/<your path>': mkdir /run/desktop/mnt/host/c: file exists

When you encounter the error above while rerunning your Docker command, it indicates that you should not mount on the second run. Here’s the initial problematic command:

docker run -it \
  -e POSTGRES_USER="root" \
  -e POSTGRES_PASSWORD="root" \
  -e POSTGRES_DB="ny_taxi" \
  -v <your path>:/var/lib/postgresql/data \
  -p 5432:5432 \
  postgres:13

To resolve the issue, use the revised command without the volume mount:

docker run -it \
  -e POSTGRES_USER="root" \
  -e POSTGRES_PASSWORD="root" \
  -e POSTGRES_DB="ny_taxi" \
  -p 5432:5432 \
  postgres:13

This error appeared when running the command:

docker build -t taxi_ingest:v001 .

The issue often arises because the user ID of the directory ny_taxi_postgres_data was changed, causing permission errors when accessing it. To resolve this error, use a directory containing only the necessary files, Dockerfile and ingest_data.py.

If you need to change permissions, use the following command on Ubuntu:

sudo chown -R $USER dir_path

On Windows, follow the instructions in this guide: The Geek Page.

For more information, refer to this explanation on Stack Overflow: Docker build error checking context.

You might have installed Docker via snap. Run the following command to verify:

sudo snap status docker

If you receive the response:

error: unknown command "status", see 'snap help'.

Then uninstall Docker and install it via the official website.

Error message: "Bind for 0.0.0.0:5432 failed: port is already allocated."

This issue occurs due to insufficient authorization rights to the host folder, which may cause it to appear empty.

Solution:

Add permission for everyone to the folder:

sudo chmod -R 777 <path_to_folder>

Example:

sudo chmod -R 777 ny_taxi_postgres_data/

This issue occurs on Ubuntu/Linux systems when attempting to rebuild the Docker container.

$ docker build -t taxi_ingest:v001 .

A folder is created to host the Docker files. When the build command is executed again, a permission error may occur because there are no permissions on this new folder. To resolve this, grant permissions by running the command:

$ sudo chmod -R 755 ny_taxi_postgres_data

If issues persist, use:

$ sudo chmod -R 777 ny_taxi_postgres_data

Note: 755 grants write access only to the owner.

Get the network name via:

docker network ls

For more details, refer to the Docker network ls documentation.

Sometimes, when you try to restart a Docker container configured with a network name, the error message appears.

To resolve this issue:

1. If the container is in a running state, stop it using:

   docker stop <container_name>
   

2. Then remove the container:

   docker rm pg-database
   

Alternatively, you can use docker start instead of docker run to restart the Docker container without removing it.

Typical error:

n.exc.OperationalError: (psycopg2.OperationalError) could not translate host name "pgdatabase" to address: Name or service not known

Solution:

1. Run docker-compose up -d to start your containers.
2. Check which network is created by Docker, as it may differ from your expectations.
3. Use the actual network name in your ingestion script instead of "pg-network".
4. Confirm the correct database service name, replacing "pgdatabase" accordingly.

Example:

• "pg-network" might become "2docker_default".

Before starting your VM, you need to enable nested virtualization. Run the following commands based on your CPU:

• For Intel CPU:

  modprobe -r kvm_intel
  modprobe kvm_intel nested=1
  

• For AMD CPU:

  modprobe -r kvm_amd
  modprobe kvm_amd nested=1
  

It’s very easy to manage your Docker container, images, network, and compose projects from VS Code.

• Install the official extension and launch it from the left side icon.

  

• It will work even if your Docker runs on WSL2, as VS Code can easily connect with your Linux.

Use the following command:

docker stop <container_id>

When you see this in logs, your container with PostgreSQL is not accepting any requests. Attempting to connect may result in the error:

connection failed: server closed the connection unexpectedly

This probably means the server terminated abnormally before or while processing the request.

To resolve this issue:

1. Delete Data Directory: Delete the directory with data (the one you map to the container using the -v flag) and restart the container.

2. Preserve Critical Data: If your data is critical, you may be able to reset the write-ahead log from within the Docker container. For more details, see here.

   docker run -it \
   -e POSTGRES_USER="root" \
   -e POSTGRES_PASSWORD="root" \
   -e POSTGRES_DB="ny_taxi" \
   -v $(pwd)/ny_taxi_postgres_data:/var/lib/postgresql/data \
   -p 5432:5432 \
   --network pg-network \
   postgres:13 \
   /bin/bash -c 'gosu postgres pg_resetwal /var/lib/postgresql/data'
   

On some versions of Ubuntu, the snap command can be used to install Docker.

sudo snap install docker

error: could not change permissions of directory "/var/lib/postgresql/data": Operation not permitted

If you have used the previous answer and created a local Docker volume, then you need to inform the compose file about the named volume:

dtc_postgres_volume_local:  # Define the named volume here

• Services mentioned in the compose file automatically become part of the same network.

Steps:

1. Use the command:

   docker volume inspect dtc_postgres_volume_local
   

   to see the location by checking the value of Mountpoint.

2. In some cases, after running docker compose up, the mounting directory created is named docker_sql_dtc_postgres_volume_local instead of the existing dtc_postgres_volume_local.

3. Rename the existing dtc_postgres_volume_local to docker_sql_dtc_postgres_volume_local:

   • Be careful when performing this operation.

4. Remove the newly created one.

5. Run docker compose up again and check if the table is there.

Couldn’t translate host name to address

Make sure the PostgreSQL database is running. Use the command to start containers in detached mode:

docker-compose up -d

Example output:

% docker compose up -d

[+] Running 2/2
⠿ Container pg-admin     Started
⠿ Container pg-database  Started

To view the containers use:

docker ps

Example output:

% docker ps

CONTAINER ID   IMAGE            COMMAND                  CREATED          STATUS          PORTS                           NAMES
faf05090972e   postgres:13      "docker-entrypoint.s…"   39 seconds ago   Up 37 seconds   0.0.0.0:5432->5432/tcp          pg-database
6344dcecd58f   dpage/pgadmin4   "/entrypoint.sh"         39 seconds ago   Up 37 seconds   443/tcp, 0.0.0.0:8080->80/tcp   pg-adminhw

To view logs for a container:

docker logs <containerid>

Example logs for PostgreSQL:

% docker logs faf05090972e

PostgreSQL Database directory appears to contain a database; Skipping initialization
2022-01-25 05:58:45.948 UTC [1] LOG:  starting PostgreSQL 13.5 (Debian 13.5-1.pgdg110+1) on aarch64-unknown-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit
2022-01-25 05:58:45.948 UTC [1] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2022-01-25 05:58:45.948 UTC [1] LOG:  listening on IPv6 address "::", port 5432
2022-01-25 05:58:45.954 UTC [1] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2022-01-25 05:58:45.984 UTC [28] LOG:  database system was interrupted; last known up at 2022-01-24 17:48:35 UTC
2022-01-25 05:58:48.581 UTC [28] LOG:  database system was not properly shut down; automatic recovery in progress
2022-01-25 05:58:48.602 UTC [28] LOG:  redo starts at 0/872A5910
2022-01-25 05:59:33.726 UTC [28] LOG:  invalid record length at 0/98A3C160: wanted 24, got 0
2022-01-25 05:59:33.726 UTC [28] LOG:  redo done at 0/98A3C128
2022-01-25 05:59:48.051 UTC [1] LOG:  database system is ready to accept connections

If docker ps doesn’t show pg-database running, use:

docker ps -a

• This will show all containers, either running or stopped.
• Get the container ID for pg-database-1 and run the appropriate command.

If you lose database data after executing docker-compose up and cannot successfully execute your ingestion script due to the following error:

sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) could not translate host name /data_pgadmin:/var/lib/pgadmin"pg-database" to address: Name or service not known

• Docker Compose may be creating its own default network since it is no longer specified in the command or file.
• Check logs after executing docker-compose up to find the network name and change the network name argument in your ingestion script.

If problems persist with pgcli, consider using HeidiSQL.

When encountering the error:

Error response from daemon: network 66ae65944d643fdebbc89bd0329f1409dec2c9e12248052f5f4c4be7d1bdc6a3 not found

Try the following steps:

1. Run docker ps -a to see all stopped and running containers.
2. Remove all containers to clean up the environment.
3. Execute docker-compose up -d again.

If facing issues connecting to the server at localhost:8080 with the error:

Unable to connect to server: could not translate host name 'pg-database' to address: Name does not resolve

Consider these solutions:

• Use a new hostname without dashes, e.g., pgdatabase.
• Make sure to specify the Docker network and use the same network in both containers in your docker-compose.yml file.

Example docker-compose.yml:

services:

  pgdatabase:
    image: postgres:13
    environment:
      - POSTGRES_USER=root
      - POSTGRES_PASSWORD=root
      - POSTGRES_DB=ny_taxi
    volumes:
      - "./ny_taxi_postgres_data:/var/lib/postgresql/data:rw"
    ports:
      - "5431:5432"
    networks:
      - pg-network

  pgadmin:
    image: dpage/pgadmin4
    environment:
      - PGADMIN_DEFAULT_EMAIL=admin@admin.com
      - PGADMIN_DEFAULT_PASSWORD=root
    ports:
      - "8080:80"
    networks:
      - pg-network

networks:
  pg-network:
    name: pg-network

When you log into PgAdmin and see an empty database, the following solution can help:

Run:

docker-compose up

And at the same time run:

docker build -t taxi_ingest:v001 .

# NETWORK NAME IS THE SAME AS THAT CREATED BY DOCKER COMPOSE
docker run -it \
  --network=pg-network \
  taxi_ingest:v001 \
  --user=postgres \
  --password=postgres \
  --host=db \
  --port=5432 \
  --db=ny_taxi \
  --table_name=green_tripdata \
  --url=${URL}

It's important to use the same --network as stated in the docker-compose.yaml file.

The docker-compose.yaml file might not specify a network, as shown below:

services:
  db:
    container_name: postgres
    image: postgres:17-alpine
    environment:
      ...
    ports:
      - '5433:5432'
    volumes:
      - ...
  pgadmin:
    container_name: pgadmin
    image: dpage/pgadmin4:latest
    environment:
      ...
    ports:
      - '8080:80'
    volumes:
      - ...

volumes:
  vol-pgdata:
    name: vol-pgdata
  vol-pgadmin_data:
    name: vol-pgadmin_data

If the network name is not specified, it is generated automatically: The name of the directory containing the docker-compose.yaml file in lowercase + _default.

You can find the network’s name when running docker-compose up:

pg-database Pulling pg-database Pulled 
Network week_1_default  Creating
Network week_1_default  Created

One common issue when running Docker Compose on GCP is that PostgreSQL might not persist its data to the specified path. For example:

services:
  ...
  pgadmin:
    ...
    volumes:
      - "./pgadmin:/var/lib/pgadmin:wr"

This setup might not work. To resolve this, use Docker Volume to make the data persist:

services:
  ...
  pgadmin:
    ...
    volumes:
      - pgadmin:/var/lib/pgadmin

volumes:
  pgadmin:

This configuration change ensures the persistence of the PGAdmin data on GCP.

The Docker engine may crash continuously and fail to work after restart. You might see error messages like "docker engine stopped" and "failed to fetch extensions" repeatedly on the screen.

Solution:

• Check if you have the latest version of Docker installed. Update Docker if necessary.
• If the problem persists, consider reinstalling Docker.
  • Note: You will need to fetch images again, but there should be no other issues.

To persist pgAdmin configuration, such as the server name, modify your docker-compose.yml by adding a "volumes" section:

services:

  pgdatabase:
    [...]

  pgadmin:
    image: dpage/pgadmin4
    environment:
      - PGADMIN_DEFAULT_EMAIL=admin@admin.com
      - PGADMIN_DEFAULT_PASSWORD=root
    volumes:
      - "./pgAdmin_data:/var/lib/pgadmin/sessions:rw"
    ports:
      - "8080:80"

In the example above, "pgAdmin_data" is a folder on the host machine, and "/var/lib/pgadmin/sessions" is the session settings folder in the pgAdmin container.

Before running docker-compose up on the YAML file, provide the pgAdmin container with access permissions to the "pgAdmin_data" folder. The container runs with a username "5050" and user group "5050". Use the following command to set permissions:

sudo chown -R 5050:5050 pgAdmin_data

This happens if you did not create the docker group and add your user. Follow these steps from the link: guides/docker-without-sudo.md at main · sindresorhus/guides · GitHub

1. Press Ctrl+D to log out and log back in again.

If you are tired of having to set up your database connection each time you start the containers, create a volume for pgAdmin:

In your docker-compose.yaml file, add the following under your pgAdmin service:

services:
  pgadmin:
    volumes:
      - type: volume
        source: pgadmin_data
        target: /var/lib/pgadmin

Also, add the following to the end of the file:

volumes:
  pgadmin_data:

This configuration will maintain the state so that pgAdmin remembers your previous connections.

This issue can occur after installing Docker Compose in a Google Cloud VM, as demonstrated in video 1.4.1.

If the downloaded Docker Compose file from GitHub is named docker-compose-linux-x86_64, you may need to rename it for convenience. Here's how to resolve the issue:

1. Rename docker-compose-linux-x86_64 to docker-compose using the following command:

   mv docker-compose-linux-x86_64 docker-compose
   

By doing this, you can use the docker-compose command directly.

Installing pass via sudo apt install pass helped to solve the issue. More about this can be found here: https://github.com/moby/buildkit/issues/1078

For those experiencing problems with Docker Compose, getting data in PostgreSQL, and similar issues, follow these steps:

• Create a new volume on Docker, either using the command line or Docker Desktop app.
• Modify your docker-compose.yml file as needed to fix any setup issues.
• Set low_memory=False when importing the CSV file using pandas:

df = pd.read_csv('yellow_tripdata_2021-01.csv', nrows=1000, low_memory=False)

• Use the specified function in your upload-data.ipynb for better tracking of the ingestion process.

from time import time

counter = 0
time_counter = 0

while True:
    t_start = time()

    df = next(df_iter)

    df.tpep_pickup_datetime = pd.to_datetime(df.tpep_pickup_datetime)
    df.tpep_dropoff_datetime = pd.to_datetime(df.tpep_dropoff_datetime)

    df.to_sql(name='yellow_taxi_data', con=engine, if_exists='append')

    t_end = time()

    t_elapsed = t_end - t_start

    print('Chunk Insertion Done! Time taken: %.2f seconds' %(t_elapsed))

    counter += 1
    time_counter += t_elapsed

    if counter == 14:
        print('All Chunks Inserted! Total Time Taken: %.2f seconds' %(time_counter))
        break

Order of Execution:

1. Open the terminal in the 2_docker_sql folder and run: docker compose up
2. Ensure no other containers are running except the ones you just executed (pgAdmin and pgdatabase).
3. Open Jupyter Notebook and begin the data ingestion.
4. Open pgAdmin and set up a server. Make sure you use the same configurations as your docker-compose.yml file, such as the same name (pgdatabase), port, and database name (ny_taxi).

To resolve this error, follow these steps:

1. Locate the config.json file for Docker, typically found in your home directory at Users/username/.docker.
2. Modify the credsStore setting to credStore.
3. Save the file and re-run your Docker Compose command.

To determine which docker-compose binary to download from Docker Compose releases, you can check your system with the following commands:

• To check the system type:

  uname -s  # This will most likely return 'Linux'
  

• To check the system architecture:

  uname -m  # This will return your system's 'flavor'
  

Alternatively, you can use the following command to download docker-compose directly:

sudo curl -L "https://github.com/docker/compose/releases/download/1.29.2/docker-compose-$(uname -s)-$(uname -m)" -o /usr/local/bin/docker-compose

If you wrote the docker-compose.yaml file exactly like the video, you might run into an error:

service "pgdatabase" refers to undefined volume dtc_postgres_volume_local: invalid compose project

To resolve this, include the volume definition in your docker-compose.yaml file by adding:

dt_postgres_volume_local:

This should be added under the volumes section. Make sure your file looks similar to this:

volumes:
  dtc_postgres_volume_local:

This error indicates that the docker-compose executable cannot be opened in the current OS. Ensure that the file you download from GitHub matches your system environment.

As of 2025/1/17, docker-compose (v2.32.4) docker-compose-linux-aarch64 does not work. Try v2.32.3 docker-compose-linux-x86_64.

This issue arises because the Postgres database is not initialized before executing docker-compose up -d. While there are other potential solutions discussed in this thread, you can resolve it by initializing the database first. Then, the Docker Compose will work as expected.

docker run -it \
  -e POSTGRES_USER="root" \
  -e POSTGRES_PASSWORD="root" \
  -e POSTGRES_DB="ny_taxi" \
  -v $(pwd)/ny_taxi_data:/var/lib/postgresql/data \
  -p 5432:5432 \
  --network=pg-network \
  --name=pg_database \
  postgres:13

initdb: error: could not change permissions of directory

WSL and Windows do not manage permissions in the same way, causing conflict if using the Windows file system rather than the WSL file system.

Solution: Use Docker volumes.

Volume is used for storage of persistent data and not for transferring files. A local volume is unnecessary.

This resolves permission issues and allows for better management of volumes.

Note: The user: is not necessary if using Docker volumes but is required if using a local drive.

services:
  postgres:
    image: postgres:15-alpine
    container_name: postgres
    user: "0:0"
    environment:
      - POSTGRES_USER=postgres
      - POSTGRES_PASSWORD=postgres
      - POSTGRES_DB=ny_taxi
    volumes:
      - "pg-data:/var/lib/postgresql/data"
    ports:
      - "5432:5432"
    networks:
      - pg-network

  pgadmin:
    image: dpage/pgadmin4
    container_name: pgadmin
    user: "${UID}:${GID}"
    environment:
      - PGADMIN_DEFAULT_EMAIL=email@some-site.com
      - PGADMIN_DEFAULT_PASSWORD=pgadmin
    volumes:
      - "pg-admin:/var/lib/pgadmin"
    ports:
      - "8080:80"
    networks:
      - pg-network

networks:
  pg-network:
    name: pg-network

volumes:
  pg-data:
  pg-admin:

Cause:

This error occurs because some applications are not updated. Specifically, check for any pending updates for Windows Terminal, WSL, and Windows Security updates.

Solution:

To update Windows Terminal:

1. Open the Microsoft Store.
2. Go to your library of installed apps.
3. Search for Windows Terminal.
4. Update the app.
5. Restart your system to apply the changes.

For updating Windows Security updates:

1. Go to Windows Updates settings.
2. Check for any pending updates, especially security updates.
3. Restart your system once the updates are downloaded and installed successfully.

Upon restarting, the same issue appears and occurs unexpectedly on Windows.

Solutions:

1. Fixing DNS Issue

   This solution is credited to reddit and has worked for some users.

   reg add "HKLM\System\CurrentControlSet\Services\Dnscache" /v "Start" /t REG_DWORD /d "4" /f
   

   Restart your computer and then re-enable it with the following command:

   reg add "HKLM\System\CurrentControlSet\Services\Dnscache" /v "Start" /t REG_DWORD /d "2" /f
   

   Restart your OS again. It should work.

2. Switch to Linux Containers

   • Right-click on the running Docker icon (next to the clock).
   • Choose "Switch to Linux containers."

bash: conda: command not found

Database is uninitialized and superuser password is not specified.

Database is uninitialized and superuser password is not specified.

Issue when trying to run the GPC VM through SSH via WSL2, likely because WSL2 isn’t looking for .ssh keys in the correct folder. The command attempted:

ssh -i gpc [username]@[my external IP]

Solutions

1. Use sudo Command

   Try using sudo before executing the command:

   sudo ssh -i gpc [username]@[my external IP]
   

2. Change Permissions

   Navigate to your folder and change the permissions for the private key SSH file:

   chmod 600 gpc
   

3. Create a .ssh Folder in WSL2

   • Navigate to your home directory:

     cd ~
     

   • Create a .ssh folder:

     mkdir .ssh
     

   • Copy the content from the Windows .ssh folder to the newly created .ssh folder:

     cp -r /mnt/c/Users/YourUsername/.ssh/* ~/.ssh/
     

   • Adjust the permissions of the files and folders in the .ssh directory if necessary.

WSL2 may not be referencing the correct .ssh/config path from Windows. You can create a config file in the home directory of WSL2 by following these steps:

1. Navigate to your home directory:

   cd ~
   

2. Create the .ssh directory:

   mkdir .ssh
   

3. Create a config file in the .ssh folder with the following content:

   HostName [GPC VM external IP]
   User [username]
   IdentityFile ~/.ssh/[private key]
   

To resolve the connection failure with PGCLI, use the following command to connect via socket:

pgcli -h 127.0.0.1 -p 5432 -u root -d ny_taxi

Ensure the database server is running and properly configured to accept connections.

In this section of the course, the 5432 port of PostgreSQL is mapped to your computer’s 5432 port. This means you can access the PostgreSQL database via pgcli directly from your computer.

So, no, you don’t need to run it inside another container. Your local system will suffice.

For a more visual and detailed explanation, feel free to check the video 1.4.2 - Port Mapping and Networks in Docker.

If you want to debug the issue on MacOS, you can try the following steps:

• Check if something is blocking your port:

  Use the lsof command to find out which application is using a specific port on your local machine:

  lsof -i :5432
  

• List running PostgreSQL services:

  Use launchctl to list running postgres services on your local machine.

• Unload the running service:

  Unload the launch agent for the PostgreSQL service, which will stop the service and free up the port:

  launchctl unload -w ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist
  

• Restart the service:

  launchctl load -w ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist
  

• Change the port:

  Changing the port from 5432:5432 to 5431:5432 can help avoid this error.

I encountered this error:

pgcli -h localhost -p 5432 -U root -d ny_taxi

Traceback (most recent call last):
  File "/opt/anaconda3/bin/pgcli", line 8, in <module>
    sys.exit(cli())
  File "/opt/anaconda3/lib/python3.9/site-packages/click/core.py", line 1128, in __call__
    return self.main(*args, **kwargs)
  File "/opt/anaconda3/lib/python3.9/site-packages/click/core.py", line 1053, in main
    rv = self.invoke(ctx)
  File "/opt/anaconda3/lib/python3.9/site-packages/click/core.py", line 1395, in invoke
    return ctx.invoke(self.callback, **ctx.params)
  File "/opt/anaconda3/lib/python3.9/site-packages/click/core.py", line 754, in invoke
    return __callback(*args, **kwargs)
  File "/opt/anaconda3/lib/python3.9/site-packages/pgcli/main.py", line 880, in cli
    os.makedirs(config_dir)
  File "/opt/anaconda3/lib/python3.9/os.py", line 225, in makedirspython
    mkdir(name, mode)
PermissionError: [Errno 13] Permission denied: '/Users/vray/.config/pgcli'

Solution 1:

This error indicates that your user doesn’t have the necessary permissions to access or modify the directory or file (/some/path/.config/pgcli). This can occur in Docker environments when privileges are assigned to root instead of the current user.

To resolve this:

1. Check the file permissions:

   ls -l /some/path/.config/pgcli
   

2. Change the ownership/permissions so that your user has the necessary permissions:

   sudo chown -R user_name /Users/user_name/.config
   

   • sudo stands for Super User DO.
   • chown means change owner.
   • -R applies recursively.
   • user_name is your PC username (e.g., vray).

Solution 2:

Make sure you install pgcli without using sudo. The recommended approach is to use conda/anaconda to avoid affecting your system Python.

If conda install gets stuck at "Solving environment," try these alternatives:

https://stackoverflow.com/questions/63734508/stuck-at-solving-environment-on-anaconda

Error:

ImportError: no pq wrapper available.

Problem Details:

• Could not import \dt
• opg 'c' implementation: No module named 'psycopg_c'
• couldn't import psycopg 'binary' implementation: No module named 'psycopg_binary'
• couldn't import psycopg 'python' implementation: libpq library not found

Solution:

1. Check Python Version:

   Ensure your Python version is at least 3.9. The 'psycopg2-binary' might fail to install on older versions like 3.7.3.

   $ python -V
   

2. Environment Setup:

   • If your Python version is not 3.9, create a new environment:

     $ conda create --name de-zoomcamp python=3.9
     $ conda activate de-zoomcamp
     

3. Install Required Libraries:

   • Install Postgres libraries:

     $ pip install psycopg2-binary
     $ pip install psycopg_binary
     

4. Upgrade pgcli:

   • If the above steps do not work, try upgrading pgcli:

     $ pip install --upgrade pgcli
     

5. Install pgcli via Conda:

   • Make sure to also install pgcli using conda:

     $ conda install -c conda-forge pgcli
     

If you follow these steps, you should be able to resolve the issue.

If your Bash prompt is stuck on the password command for postgres:

Use winpty:

winpty pgcli -h localhost -p 5432 -u root -d ny_taxi

Alternatively, try using Windows Terminal or the terminal in VS Code.

The error above was faced continually despite inputting the correct password.

1. Stop the PostgreSQL service on Windows

2. Using WSL: Completely uninstall PostgreSQL 12 from Windows and install postgresql-client on WSL:

sudo apt install postgresql-client-common postgresql-client libpq-dev

3. Change the port of the Docker container

4. Keep the Database Connection:

If you encounter the error:

PGCLI -connection failed: FATAL: password authentication failed for user "root"

It might be because the connection to the Postgres:13 image was closed. Ensure you keep the database connected in order to continue with the tutorial steps, using the following command:

docker run -it \
   -e POSTGRES_USER=root \
   -e POSTGRES_PASSWORD=root \
   -e POSTGRES_DB=ny_taxi \
   -v d:/git/data-engineering-zoomcamp/week_1/docker_sql/ny_taxi_postgres_data:/var/lib/postgresql/data \
   -p 5432:5432 \
   postgres:13

You should see this

2024-01-26 20:14:43.124 UTC [1] LOG:  database system is ready to accept connections

5. Change the Port for Docker PostgreSQL:

   After running the command pgcli -h localhost -p 5432 -u root -d ny_taxi, if prompted for a password, the error may persist due to local Postgres installation. To resolve this port conflict between host and container:

   • Configure your Docker PostgreSQL container to use a different port. Map it to a different port on your host machine:

docker run -it \
   -e POSTGRES_USER="root" \
   -e POSTGRES_PASSWORD="root" \
   -e POSTGRES_DB="ny_taxi" \
   -v c:/workspace/de-zoomcamp/1_intro_to_data_engineering/docker_sql/ny_taxi_postgres_data:/var/lib/postgresql/data \
   -p 5433:5432 \
   postgres:13

• 5433 refers to the port on the host machine.
• 5432 refers to the port inside the Docker Postgres container.

Problem

If you have already installed pgcli but Bash or the Windows Terminal doesn't recognize the command:

• On Git Bash:

  bash: pgcli: command not found
  

• On Windows Terminal:

  pgcli: The term 'pgcli' is not recognized…
  

Solution

Try adding the Python path to the Windows PATH variable:

1. Use the command to get the location:

   pip list -v
   

2. Copy the path, which looks like:

   C:\Users\...\AppData\Roaming\Python\Python39\site-packages
   

3. Replace site-packages with Scripts:

   C:\Users\...\AppData\Roaming\Python\Python39\Scripts
   

It might be that Python is installed elsewhere. For example, it could be under:

• c:\python310\lib\site-packages

In that case, you should add:

• c:\python310\lib\Scripts to PATH.

Instructions

• Add the determined path to Path (or PATH) in System Variables.

Reference

Stack Overflow Reference

If running pgcli locally causes issues or you do not want to install it on your machine, you can use it within a Docker container instead.

Below is the usage with values used in the course videos for:

• Network name (Docker network)
• Postgres-related variables for pgcli
• Hostname
• Username
• Port
• Database name

docker run -it --rm --network pg-network ai2ys/dockerized-pgcli:4.0.1

Then execute the following pgcli command:

pgcli -h pg-database -U root -p 5432 -d ny_taxi

You'll be prompted for the password for the user root.

Example Output:

Server: PostgreSQL 16.1 (Debian 16.1-1.pgdg120+1)
Version: 4.0.1
Home: [pgcli.com](http://pgcli.com)

To list tables:

root@pg-database:ny_taxi> \dt

+--------+------------------+-------+-------+
| Schema | Name             | Type  | Owner |
|--------+------------------+-------+-------|
| public | yellow_taxi_data | table | root  |
+--------+------------------+-------+-------+

SELECT 1
Time: 0.009s
root@pg-database:ny_taxi>

PULocationID will not be recognized, but "PULocationID" will be. This is because unquoted identifiers are case insensitive. See docs.

When using the command \d <database name> you get the error column c.relhasoids does not exist.

Resolution:

1. Uninstall pgcli.
2. Reinstall pgcli.
3. Restart your PC.

Issue

When attempting to start the Docker Postgres container, you may encounter the error message:

Error - postgres port is already in use.

Solutions

Option 1: Identify and Stop the Service

1. Determine which service is using the port by running:

   sudo lsof -i :5432
   

2. Stop the service that is using the port:

   sudo service postgresql stop
   

Option 2: Map to a Different Port

For a more long-term solution, consider mapping to a different port:

• Map local port 5433 to container port 5432 in your Docker configuration (Dockerfile or docker-compose.yml).
• If using a VM, ensure that port 5433 is forwarded in the host machine configuration.

This approach prevents conflicts and allows the Docker Postgres container to run without interruption.

The error persists because the psycopg library cannot find the required libpq library. Ensure the required PostgreSQL client library is installed:

sudo apt install libpq-dev

Rebuild psycopg:

1. Uninstall the existing packages:

   pip uninstall psycopg psycopg_binary psycopg_c -y
   

2. Reinstall psycopg:

   pip install psycopg --no-binary psycopg
   

The issue should be resolved by now. However, if you still encounter the error:

ModuleNotFoundError: No module named 'psycopg2'

Then run the following:

pip install psycopg2-binary

This error occurs when uploading data via a connection in Jupyter Notebook:

engine = create_engine('postgresql://root:root@localhost:5432/ny_taxi')

Possible Solutions:

1. Port Conflict:

   • Port 5432 might be occupied by another Postgres installation on your local machine. This can lead to your connection not reaching Docker.
   • Try using a different port, such as 5431, or verify the port mapping.
   • Alternatively, remove any old or unnecessary Postgres installations if they're not in use.

2. Windows Service Check:

   • Check for any running services on Windows that might be using Postgres.
   • Stopping such services might resolve the issue.

To resolve the issue of a failed connection to PostgreSQL due to password authentication, consider the following steps:

• Check Port Usage: Ensure that port 5432 is properly forwarded. If it is being used by another process, follow these steps to kill it:

  sudo lsof -i :5432
  sudo kill -9 PID
  

• For Windows Users: If PostgreSQL is running locally and pgAdmin4 is using the 5432 port, follow these instructions:

  1. Press Win + R to open the Run dialog.
  2. Type services.msc and press Enter.
  3. In the Services window, scroll down to find a service named like PostgreSQL, postgresql-x64-13, or similar, depending on your PostgreSQL version.
  4. Right-click the PostgreSQL service and select Stop.

This error can occur in the following scenarios:

• Using pgcli:

  pgcli -h localhost -p 5432 -U root -d ny_taxi
  

• Uploading data via a connection in a Jupyter notebook:

  engine = create_engine('postgresql://root:root@localhost:5432/ny_taxi')
  

Solutions:

1. Port Change:

   • Change the port from 5432 to another port (e.g., 5431).
   • Example: Change 5432:5432 to 5431:5432.

2. User Change:

   • Change POSTGRES_USER=root to PGUSER=postgres.

3. Docker Solution:

   • Run docker compose down.
   • Remove the folder containing the Postgres volume.
   • Run docker compose up again.

Additional Resources:

For more details, refer to this Stack Overflow discussion.

OperationalError: (psycopg2.OperationalError) connection to server at "localhost" (::1), port 5432 failed: FATAL:  database "ny_taxi" does not exist

Make sure PostgreSQL is running. You can check that by running:

docker ps

Solution:

• If you have PostgreSQL software installed on your computer previously, consider building your instance on a different port, such as 8080, instead of 5432.

Issue:

ModuleNotFoundError: No module named 'psycopg2'

IMAGE:image_1

IMAGE:image_2

Solution:

1. Install psycopg2-binary:

   pip install psycopg2-binary
   

2. If psycopg2-binary is already installed, update it:

   pip install psycopg2-binary --upgrade
   

3. Other methods if the above fails:

   • If the error persists, update conda:

     conda update -n base -c defaults conda
     

   • Alternatively, update pip:

     pip install --upgrade pip
     

   • Reinstall psycopg:

     • Uninstall the psycopg package.
     • Update conda or pip.
     • Reinstall psycopg using pip.

   • If an error shows about pg_config not being found, install PostgreSQL:

     • On Mac, use:

       brew install postgresql
       

In join queries, if you mention the column name directly or enclose it in single quotes, you'll encounter an error saying "column does not exist".

Solution: Enclose the column names in double quotes, and it will work correctly.

pgAdmin has a new version. The create server dialog may not appear. Try using Register -> Server instead.

Using GitHub Codespaces in the browser resulted in a blank screen after logging into pgAdmin (running in a Docker container). The terminal of the pgAdmin container was showing the following error message:

CSRFError: 400 Bad Request: The referrer does not match the host.

Solution #1:

As recommended in the following issue: GitHub Issue #5432, setting the following environment variable solved it:

docker run --rm -it \
  -e PGADMIN_DEFAULT_EMAIL="admin@admin.com" \
  -e PGADMIN_DEFAULT_PASSWORD="root" \
  -e PGADMIN_CONFIG_WTF_CSRF_ENABLED="False" \
  -p "8080:80" \
  --name pgadmin \
  --network=pg-network \
  dpage/pgadmin4:8.2

Solution #2:

Using the locally installed VSCode to display GitHub Codespaces. When using GitHub Codespaces in the locally installed VSCode (opening a Codespace or creating/starting one), this issue did not occur.

I am using a Mac Pro device and connect to the GCP Compute Engine via Remote SSH - VSCode. But when trying to run the PgAdmin container via docker run or docker compose, I couldn't access the PgAdmin address via my browser. After modifications, I was able to access it.

Solution #1:

Modify the docker run command:

docker run --rm -it \
  -e PGADMIN_DEFAULT_EMAIL="admin@admin.com" \
  -e PGADMIN_DEFAULT_PASSWORD="pgadmin" \
  -e PGADMIN_CONFIG_WTF_CSRF_ENABLED="False" \
  -e PGADMIN_LISTEN_ADDRESS=0.0.0.0 \
  -e PGADMIN_LISTEN_PORT=5050 \
  -p 5050:5050 \
  --network=de-zoomcamp-network \
  --name pgadmin-container \
  --link postgres-container \
  -t dpage/pgadmin4

Solution #2:

Modify the docker-compose.yaml configuration and use the docker compose up command:

pgadmin:
  image: dpage/pgadmin4
  container_name: pgadmin-container
  environment:
    - PGADMIN_DEFAULT_EMAIL=admin@admin.com
    - PGADMIN_DEFAULT_PASSWORD=pgadmin
    - PGADMIN_CONFIG_WTF_CSRF_ENABLED=False
    - PGADMIN_LISTEN_ADDRESS=0.0.0.0
    - PGADMIN_LISTEN_PORT=5050
  volumes:
    - "./pgadmin_data:/var/lib/pgadmin/data"
  ports:
    - "5050:5050"
  networks:
    - de-zoomcamp-network
  depends_on:
    - postgres-container

To keep pgAdmin settings after restarting the container, follow these steps:

1. Create the directory for pgAdmin data:

   mkdir -p /path/to/pgadmin-data
   

2. Assign ownership to pgAdmin's user (ID 5050):

   sudo chown -R 5050:5050 /path/to/pgadmin-data
   

3. Set the appropriate permissions:

   sudo chmod -R 755 /path/to/pgadmin-data
   

This error occurs when connecting pgAdmin with Docker Postgres. In the tutorial, the pgAdmin server creation under Connection > Host name/address uses pg-database and results in the above-mentioned error when saved.

Solution 1:

• Verify that both containers are connected to pg-network:

  docker network inspect pg-network
  

• If the Docker Postgres container is not connected, connect it to pg-network:

  docker network connect pg-network postgresContainer_name
  

• Retry the connection. If the error persists, instead of using pg-database under Connection > Host name/address, try using the IP Address:

  • Use the IP address of the postgresContainer_name container (e.g., 172.19.0.3) in the pgAdmin configuration instead of the container name or pg-database.

ImportError: DLL load failed while importing _sqlite3: The specified module could not be found. 
ModuleNotFoundError: No module named 'pysqlite2'

The issue may arise due to the absence of sqlite3.dll in the path ".\Anaconda\Dlls\".

To resolve the issue:

1. Copy the sqlite3.dll file from \Anaconda3\Library\bin.
2. Paste the file into the ".\Anaconda\Dlls\" directory.

This solution applies if you are using Anaconda.

If you follow the video 1.2.2 - Ingesting NY Taxi Data to Postgres and execute the same steps, you will ingest all the data (~1.3 million rows) into the table yellow_taxi_data. However, running the whole script in the Jupyter notebook for a second time from top to bottom will result in missing the first chunk of 100,000 records. This occurs because a call to the iterator appears before the while loop, leading to the second chunk being ingested first.

Solution:

• Remove the cell df=next(df_iter) located higher up in the notebook than the while loop.
• Ensure the first w(df_iter) call is within the while loop.

📔 Note: The notebook is used to test the code and is not intended to be run top to bottom. The logic is organized in a later step when inserted into a .py file for the pipeline.

Pandas can interpret "string" column values as "datetime" directly when reading the CSV file using pd.read_csv with the parse_dates parameter. This can include a list of column names or column indices, eliminating the need for conversion afterward.

Reference: pandas.read_csv documentation

Example from Week 1:

import pandas as pd

df = pd.read_csv(
    'yellow_tripdata_2021-01.csv',
    nrows=100,
    parse_dates=['tpep_pickup_datetime', 'tpep_dropoff_datetime']
)

df.info()

Output:

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 100 entries, 0 to 99
Data columns (total 18 columns):
 #   Column                 Non-Null Count  Dtype          
---  ------                 --------------  -----          
 0   VendorID               100 non-null    int64          
 1   tpep_pickup_datetime   100 non-null    datetime64[ns] 
 2   tpep_dropoff_datetime  100 non-null    datetime64[ns] 
 3   passenger_count        100 non-null    int64          
 4   trip_distance          100 non-null    float64        
 5   RatecodeID             100 non-null    int64          
 6   store_and_fwd_flag     100 non-null    object         
 7   PULocationID           100 non-null    int64          
 8   DOLocationID           100 non-null    int64          
 9   payment_type           100 non-null    int64          
 10  fare_amount            100 non-null    float64        
 11  extra                  100 non-null    float64        
 12  mta_tax                100 non-null    float64        
 13  tip_amount             100 non-null    float64        
 14  tolls_amount           100 non-null    float64        
 15  improvement_surcharge  100 non-null    float64        
 16  total_amount           100 non-null    float64        
 17  congestion_surcharge   100 non-null    float64        
dtypes: datetime64[ns](2), float64(9), int64(6), object(1)
memory usage: 14.2+ KB

When a CSV file is compressed using Gzip, it is saved with a ".csv.gz" file extension. This file type is also known as a Gzip compressed CSV file. To read a Gzip compressed CSV file using Pandas, you can use the read_csv() function.

Here is an example of how to read a Gzip compressed CSV file using Pandas:

import pandas as pd

df = pd.read_csv('file.csv.gz',
                 compression='gzip',
                 low_memory=False)

Contrary to pandas’ read_csv method, there’s no simple way to iterate through and set chunksize for parquet files. We can use PyArrow (Apache Arrow Python bindings) to resolve that.

import pyarrow.parquet as pq
from sqlalchemy import create_engine
import time

output_name = "https://d37ci6vzurychx.cloudfront.net/trip-data/yellow_tripdata_2021-01.parquet"

parquet_file = pq.ParquetFile(output_name)
parquet_size = parquet_file.metadata.num_rows

engine = create_engine(f'postgresql://{user}:{password}@{host}:{port}/{db}')

table_name = "yellow_taxi_schema"

# Clear table if exists
pq.read_table(output_name).to_pandas().head(n=0).to_sql(name=table_name, con=engine, if_exists='replace')

# Default (and max) batch size
index = 65536

for i in parquet_file.iter_batches(use_threads=True):
    t_start = time.time()
    print(f'Ingesting {index} out of {parquet_size} rows ({index / parquet_size:.0%})')
    i.to_pandas().to_sql(name=table_name, con=engine, if_exists='append')
    index += 65536
    t_end = time.time()
    print(f'\t- it took %.1f seconds' % (t_end - t_start))

The following error occurs during the execution of a Jupyter notebook cell:

from sqlalchemy import create_engine

Solution:

The issue can be resolved by ensuring the version of the Python module typing_extensions is 4.6.0 or later. You can update it using either Conda or pip:

• Using Conda:

  conda update typing_extensions
  

• Using pip:

  pip install --upgrade typing_extensions
  

For more details, you can refer to the changelog for typing_extensions 4.6.0.

When using create_engine('postgresql://root:root@localhost:5432/ny_taxi'), you may encounter the error:

TypeError: 'module' object is not callable

Use the correct connection string syntax:

conn_string = "postgresql+psycopg://root:root@localhost:5432/ny_taxi"
engine = create_engine(conn_string)

Error raised during the Jupyter Notebook cell execution:

engine = create_engine('postgresql://root:root@localhost:5432/ny_taxi')

Solution:

Install the Python module psycopg2. It can be installed using Conda or pip:

• Using Conda:

  conda install psycopg2
  

• Using pip:

  pip install psycopg2
  

Error raised during the Jupyter notebook’s cell execution:

conn_string = "postgresql+psycopg://root:root@localhost:5432/ny_taxi"

engine = create_engine(conn_string)

Solution:

We had a scenario of a virtual environment (created by PyCharm) being run on top of another virtual environment (on conda). The solution was:

1. Remove the .venv.

2. Create a new virtual environment with conda:

   conda create -n pyingest python=3.12
   

3. Install the required dependencies:

   pip install pandas sqlalchemy psycopg2-binary jupyterlab
   

4. Re-execute the code.

For psycopg2, the connection string should be:

postgresql+psycopg2://{db_user}:{db_password}@{db_host}:{db_port}/{db_name}

Reference - Kayla Tinker 1/14/25

First, check the versions of SQLAlchemy and Pandas to ensure they are both up-to-date. You can upgrade them using pip or conda if needed.

Then, try to wrap the query using text:

from sqlalchemy import text

query = text("SELECT * FROM tbl")
df = pd.read_sql_query(query, conn)

When you set up a VM in Google Cloud Platform (GCP), it initially uses an ephemeral IP address, which changes each time you start or stop the VM. If you need a consistent IP for your configuration file, you should set up a static IP address.

Steps to Set Up a Static IP Address

1. Navigate to VPC Network > IP addresses in the GCP console.
2. Allocate a new static IP address.
3. Attach the static IP to your VM instance.

Note: You are charged for a static IP if it is not allocated to a specific VM, so make sure it is attached to avoid extra fees.

For detailed instructions, consult the GCP documentation.

Issue

Windows error:

The installer is unable to automatically update your system PATH. Please add C:\tools\google-cloud-sdk\bin

Solution

If you encounter this error frequently, consider the following steps:

1. Add Gitbash to Windows Path:

   • Using Conda:
     • Download the Anaconda Navigator.
     • During installation, check the box to add Conda to the path (even though it's not recommended).

2. Install Git Bash:

   • If not installed, install Git Bash.
   • If installed, consider reinstalling it.
   • During installation, ensure you check:
     • Add GitBash to Windows Terminal
     • Use Git and optional Unix tools from the command prompt

3. Setup Git Bash:

   • Open Git Bash and type the following command:

     conda init bash
     

   • This will modify your bash profile.

4. Set Gitbash as Default Terminal:

   • Open the Windows Terminal.
   • Go to settings.
   • Change the default profile from Windows PowerShell to Git Bash.

By following these steps, you should be able to add the Google Cloud SDK path to your system on Windows without issues.

When creating a project in GCP, you may encounter the following error:

WARNING: Project creation failed: HttpError accessing cloudresourcemanager.googleapis.com: response: {
  'content-type': 'application/json; charset=UTF-8',
  'status': 409
}, content {
  "error": {
    "code": 409,
    "message": "Requested entity already exists",
    "status": "ALREADY_EXISTS"
  }
}

Explanation

This error occurs when the project ID you are trying to use is already taken. Project IDs are unique across all GCP projects. If any user ever had a project with that ID, you cannot use it.

Solution

• Choose a different, more unique project ID. Avoid common names like testproject as they are likely to be already in use.

For more details, refer to the discussion: Stack Overflow

If you receive the error:

Error 403: The project to be billed is associated with an absent billing account., accountDisabled

It is most likely because you did not enter your project ID correctly. The value you enter should be unique to your project. You can find this value on your GCP Dashboard when you log in.

Another possibility is that you have not linked your billing account to your current project.

If Google refuses your credit/debit card, try using a different one. For instance, a card from Kaspi (Kazakhstan) might not work, but a card from TBC (Georgia) does.

Unfortunately, support assistance might not be highly effective in resolving this issue.

Additionally, a Pyypl web-card can be a viable alternative.

ny-rides.json

The ny-rides.json is your private file in Google Cloud Platform (GCP). Here’s how to find it:

• Navigate to GCP and select the project with your instance.
• Go to IAM & Admin.
• Select the Service Accounts tab.
• Click the Keys tab.
• Add a key, choosing JSON as the key type, then click Create.

Note: Once in the Service Accounts, click the email associated to access the KEYS tab where you can add a key as a JSON key type.

You likely didn’t enable the Compute Engine API.

In this lecture, Alexey deleted his instance in Google Cloud. Do I have to do it?

No, do not delete your instance in Google Cloud Platform. Otherwise, you will have to set it up again for the week 1 readings.

Initially, I could not SSH into my VM from my Windows laptop. I thought it was because I did not follow the tutorial exactly. Instead of generating the SSH key using MINGW/git bash with the Linux-style command, I did it in Command Prompt using the Windows-style command. I kept getting a public key error.

Permanent Solution:

It turns out it wasn’t an issue with the key generation at all! The problem was with the username. I had given my SSH key a different username than what appeared in my VM (my Google account username). So, I had been trying to log in with googleacctuser@[ipaddr] instead of mySSHuser@[ipaddr]. Here's how I resolved it:

1. Retraced my steps to check the SSH key setup in the GCP console, where it showed the user and SSH key.
2. Changed the username to the correct one (googleacctuser) in my config file.
3. Updated the config file and used mySSHuser to log in.

Now, the issue was that I had created two users. I made all the installations and permissions on googleacctuser, not accessible from mySSHuser. Since I didn't need mySSHuser, I edited the SSH key to change the username at the end and updated the GCP console and config file accordingly.

Then, I planned to delete the mySSHuser account in the VM terminal to keep things clean (though I got a bit attached, so I skipped this).

Temporary Solution:

Before figuring out my issue, I used a shortcut by SSH'ing into the VM in the browser, which worked nicely for a while. But eventually, I needed to use VSCode.

If you are progressing through the course and find that your VM is starting to become slow, you can run the following commands to inspect and detect areas where you can improve:

Recommended VM Size

• Start with a 60GB machine. A 30GB machine may not be sufficient, as you might need to restart the project with a larger size.

Commands to Inspect the Health of Your VM

• System Resource Usage

  top
  htop
  

  Shows real-time information about system resource usage, including CPU, memory, and processes.

  free -h
  

  Displays information about system memory usage and availability.

  df -h
  

  Shows disk space usage of file systems.

  du -h <directory>
  

  Displays disk usage of a specific directory.

• Running Processes

  ps aux
  

  Lists all running processes along with detailed information.

• Network

  ifconfig
  ip addr show
  

  Shows network interface configuration.

  netstat -tuln
  

  Displays active network connections and listening ports.

• Hardware Information

  lscpu
  

  Displays CPU information.

  lsblk
  

  Lists block devices (disks and partitions).

  lshw
  

  Lists hardware configuration.

• User and Permissions

  who
  

  Shows who is logged on and their activities.

  w
  

  Displays information about currently logged-in users and their processes.

• Package Management

  apt list --installed
  

  Lists installed packages (for Ubuntu and Debian-based systems).

If you’ve got the error:

Error: Error updating Dataset "projects/<your-project-id>/datasets/demo_dataset": googleapi: Error 403: Billing has not been enabled for this project. Enable billing at console.cloud.google.com. The default table expiration time must be less than 60 days, billingNotEnabled

but you’ve set your billing account, try disabling billing for the project and enabling it again. This method has been successful for others.

If you are encountering installation trouble with the Google Cloud SDK on Windows and receiving the following error:

These credentials will be used by any library that requests Application Default Credentials (ADC).

WARNING:

Cannot find a quota project to add to ADC. You might receive a "quota exceeded" or "API not enabled" error. Run $ gcloud auth application-default set-quota-project to add a quota project.

Try these steps:

1. Reinstall the SDK using the unzip file "install.bat".
2. Check the installation by running gcloud version.
3. Run gcloud init to set up your project.
4. Execute gcloud auth application-default login.

For detailed instructions, refer to the following guide: Windows SDK Installation Guide

1. Click on your VM.
2. Create an image of your VM.
3. On the page of the image, tell GCP to create a new VM instance via the image.
4. On the settings page, change the location.

The reason this video about the GCP VM exists is that many students had problems configuring their environment. You can use your own environment if it works for you.

Advantages of using your own environment include:

• Commit Changes: If you are working in a GitHub repository, you will be able to commit changes directly. In the VM, the repo is cloned via HTTPS, so it is not possible to commit directly, even if you are the owner of the repo.

If you encounter an error while trying to create a directory:

User1@DESKTOP-PD6UM8A MINGW64 /

$ mkdir .ssh

mkdir: cannot create directory ‘.ssh’: Permission denied

This error occurs because you are attempting to create the directory in the root folder (/).

To resolve this, create the directory in your home directory instead. Use the following steps:

1. Navigate to your home directory using:

   cd ~
   

2. Create the .ssh directory:

   mkdir .ssh
   

For further guidance, watch this video.

Failed to save '<file>': Unable to write file 'vscode-remote://ssh-remote+de-zoomcamp/home/<user>/data_engineering_course/week_2/airflow/dags/<file>' (NoPermissions (FileSystemError): Error: EACCES: permission denied, open '/home/<user>/data_engineering_course/week_2/airflow/dags/<file>')

To resolve this issue, you need to change the owner of the files you are trying to edit via VS Code. Follow these steps:

1. Connect to your VM using SSH.

2. Run the following command to change the ownership:

   sudo chown -R <user> <path to your directory>
   

Question: I connected to my VM perfectly fine last week (SSH) but when I tried again this week, the connection request keeps timing out.

Answer:

1. Start Your VM: Make sure the VM is running in your GCP console.

2. Update External IP:

   • Copy its External IP once the VM is running.
   • Update your SSH configuration file with this IP.

3. Edit SSH Config:

   cd ~/.ssh
   code config
   

   This command opens the config file in VSCode for editing.

Go to edit your VM.

1. Navigate to the Automation section.

2. Add the following Startup script:

   #!/bin/bash
   sudo ufw allow ssh
   

3. Stop and Start the VM.

You can easily forward the ports of pgAdmin, PostgreSQL, and Jupyter Notebook using the built-in tools in Ubuntu without any additional client:

1. On the VM machine:

   • Launch Docker and Jupyter Notebook in the correct folder using:

     docker-compose up -d
     jupyter notebook
     

2. From the local machine:

   • Execute:

     ssh -i ~/.ssh/gcp -L 5432:localhost:5432 username@external_ip_of_vm
     

   • Execute the same command for ports 8080 and 8888.

3. Accessing Applications Locally:

   • For pgAdmin, open a browser and go to localhost:8080.
   • For Jupyter Notebook, open a browser and go to localhost:8888.
     • If you encounter issues with credentials, you may need to copy the link with the access token from the terminal logs on the VM when you launched the Jupyter Notebook.

4. Forwarding Both pgAdmin and PostgreSQL:

   • Use:

     ssh -i ~/.ssh/gcp -L 5432:localhost:5432 -L 8080:localhost:8080 modito@35.197.218.128
     

If you are using MS VS Code and running gcloud in WSL2, when you first try to login to GCP via the gcloud CLI with gcloud auth application-default login, you may encounter an issue where a message appears and nothing happens:

There might be a prompt asking if you want to open it via a browser. If you click on it, it will open a page with an error message:

Solution:

• Hover over the long link.
• Ctrl + Click the long link.
• Click "Configure Trusted Domains here."
• A popup will appear; pick the first or second entry.

Next time you run gcloud auth, the login page should pop up via the default browser without issues.

This error typically occurs due to internet connectivity issues. Terraform is unable to access the online registry.

Solution:

• Check your VPN/Firewall settings.
• Clear cookies or restart your network.
• Run terraform init again after addressing the connection issues.

The issue was related to network restrictions, as Google is not accessible in my country. I used a VPN and discovered that the terminal program does not automatically follow the system proxy, requiring separate proxy configuration settings.

Solution:

1. Open an Enhanced Mode in your VPN application, such as Clash.
2. Run terraform apply again.

If you encounter this issue, consult your VPN provider for assistance with configuration.

You can configure Terraform on Windows 10 using the Linux Subsystem (WSL) by following this guide: Configuring Terraform on Windows 10 Linux Subsystem.

For more information, you can refer to the following issue on GitHub:

HashiCorp Terraform Issue #14513

When running:

terraform apply

on WSL2, you might encounter the following error:

Error: Post "https://storage.googleapis.com/storage/v1/b?alt=json&prettyPrint=false&project=<your-project-id>": oauth2: cannot fetch token: 400 Bad Request

Response: {"error":"invalid_grant","error_description":"Invalid JWT: Token must be a short-lived token (60 minutes) and in a reasonable timeframe. Check your iat and exp values in the JWT claim."}

This issue occurs due to potential time desynchronization on your machine, affecting JWT computation.

To fix this, run the following command to synchronize your system time:

sudo hwclock -s

│ Error: googleapi: Error 403: Access denied., forbidden

Your $GOOGLE_APPLICATION_CREDENTIALS might not be pointing to the correct file. Try the following steps:

1. Set the correct path for your credentials:

   export GOOGLE_APPLICATION_CREDENTIALS=~/.gc/YOUR_JSON.json
   

2. Activate the service account:

   gcloud auth activate-service-account --key-file $GOOGLE_APPLICATION_CREDENTIALS
   

One service account is enough for all the services/resources you'll use in this course. After you get the file with your credentials and set your environment variable, you should be good to go.

Here: https://releases.hashicorp.com/terraform/1.1.3/terraform_1.1.3_linux_amd64.zip

This error occurs when terraform init is run outside the working directory.

To resolve this issue:

1. Navigate to the working directory that contains your Terraform configuration files.
2. Run the terraform init command inside the correct directory.

Make sure your configuration files (e.g., .tf files) are present in the directory before running the command.

The error:

Error: googleapi: Error 403: Access denied., forbidden

Error: Error creating Dataset: googleapi: Error 403: Request had insufficient authentication scopes.

Solution:

1. Verify your credentials by running:

   echo $GOOGLE_APPLICATION_CREDENTIALS
   echo $?
   

2. Ensure you have set the GOOGLE_APPLICATION_CREDENTIALS environment variable correctly, as demonstrated in the environment setup video in week 1:

   export GOOGLE_APPLICATION_CREDENTIALS="<path/to/your/service-account-authkeys>.json"
   

The error:

Error: googleapi: Error 403: terraform-trans-campus@trans-campus-410115.iam.gserviceaccount.com does not have storage.buckets.create access to the Google Cloud project. Permission 'storage.buckets.create' denied on resource (or it may not exist)., forbidden

The solution:

You have to declare the project name as your Project ID, not your Project name, available on the GCP console Dashboard.

To ensure the sensitivity of the credentials file, use the following configuration:

provider "google" {
  project     = var.projectId
  credentials = file("${var.gcpkey}")
  #region      = var.region
  zone = var.zone
}

When running terraform destroy, the following error can occur:

Do you really want to destroy all resources?

Terraform will destroy all your managed infrastructure, as shown above.

There is no undo. Only 'yes' will be accepted to confirm.

Enter a value: yes

google_bigquery_dataset.homework_dataset: Destroying... [id=projects/terraform-demo-449214/datasets/homework_dataset]

╷

│ Error: Error when reading or editing Dataset: googleapi: Error 400: Dataset terraform-demo-449214:homework_dataset is still in use, resourceInUse

This is because the dataset is still in use by a table. To delete the dataset, set the delete_contents_on_destroy property to true in the main.tf file.

For this issue, you can use the following solution:

SELECT * FROM zones AS z WHERE z."Zone" = 'Astoria Zone';

Columns that start with uppercase sometimes need to be enclosed in double quotes.

Additionally, check your dataset for the existence of 'Astoria Zone'. You might find only 'Astoria':

SELECT * FROM zones AS z WHERE z."Zone" = 'Astoria';

It is inconvenient to use quotation marks all the time, so it is better to put the data in the database all in lowercase. In Pandas, after:

import pandas as pd

df = pd.read_csv('taxi+_zone_lookup.csv')

Add the row:

df.columns = df.columns.str.lower()

To resolve this, ensure that your config file is in C/User/Username/.ssh/config.

If you use Anaconda (recommended for the course), it comes with pip, so the issue is probably that Anaconda’s Python is not on the PATH.

For Linux and MacOS:

1. Open a terminal.

2. Find the path to your Anaconda installation. This is typically ~/anaconda3 or ~/opt/anaconda3.

3. Add Anaconda to your PATH with the command:

   export PATH="/path/to/anaconda3/bin:$PATH"
   

4. To make this change permanent, add the command to your .bashrc (Linux) or .bash_profile (MacOS) file.

On Windows, using Git Bash:

1. Locate your Anaconda installation. The default path is usually C:\Users\[YourUsername]\Anaconda3.

2. Convert the Windows path to a Unix-style path for Git Bash, e.g., C:\Users\[YourUsername]\Anaconda3 becomes /c/Users/[YourUsername]/Anaconda3.

3. Add Anaconda to your PATH with the command:

   export PATH="/c/Users/[YourUsername]/Anaconda3/:/c/Users/[YourUsername]/Anaconda3/Scripts/$PATH"
   

4. To make this change permanent, add the command to your .bashrc file in your home directory.

5. Refresh your environment with the command:

   source ~/.bashrc
   

For Windows (without Git Bash):

1. Right-click on 'This PC' or 'My Computer' and select 'Properties'.
2. Click on 'Advanced system settings'.
3. In the System Properties window, click on 'Environment Variables'.
4. In the Environment Variables window, select the 'Path' variable in the 'System variables' section and click 'Edit'.
5. In the Edit Environment Variable window, click 'New' and add the path to your Anaconda installation (typically C:\Users\[YourUsername]\Anaconda3 and C:\Users\[YourUsername]\Anaconda3\Scripts).
6. Click 'OK' in all windows to apply the changes.

After adding Anaconda to the PATH, you should be able to use pip from the command line. Remember to restart your terminal (or command prompt in Windows) to apply these changes.

Resolution: You need to stop the service using the port.

Run the following:

sudo kill -9 `sudo lsof -t -i:<port>`

Replace <port> with 8080 in this case. This will free up the port for use.

To get a pip-friendly requirements.txt file from Anaconda, use the following steps:

1. Install pip in your Anaconda environment:

   conda install pip
   

2. Generate the requirements.txt file:

   pip list --format=freeze > requirements.txt
   

Note:

• conda list -d > requirements.txt will not work.
• pip freeze > requirements.txt may give odd pathing.

Install and Open Jupyter Notebook

To install Jupyter Notebook, run:

pip install jupyter

To open Jupyter Notebook, use:

python3 -m notebook

Convert Jupyter Notebook to Python Script

First, ensure nbconvert is installed and upgraded:

pip install nbconvert --upgrade

Then, convert a Jupyter Notebook to a Python script with the following command:

python3 -m jupyter nbconvert --to=script upload-data.ipynb

If you keep getting errors with nbconvert after executing:

jupyter nbconvert --to script <your_notebook.ipynb>

You could try converting your Jupyter notebook using another tool called Jupytext. Jupytext is an excellent tool for converting Jupyter Notebooks to Python scripts, similar to nbconvert.

1. Install Jupytext

   pip install jupytext
   

2. Convert your Notebook to a Python script

   jupytext --to py <your_notebook.ipynb>
   

If you are using Windows, try the following steps to resolve the error:

1. Copy the .ssh folder from the Linux file path to Windows.

2. In the config file, use:

   IdentityFile C:\Users\<username>\.ssh\gcp
   

   Instead of:

   IdentityFile ~/.ssh/gcp
   

3. Ensure the private key file located at C:\Users\<username>\.ssh\gcp has an extra line at the end:

   

• Prefect FAQ Document
• Airflow FAQ Document
• Mage FAQ Document

To launch Kestra, follow these instructions:

For Linux

Start Docker with the following command:

docker run \
  --pull=always \
  --rm \
  -it \
  -p 8080:8080 \
  --user=root \
  -v /var/run/docker.sock:/var/run/docker.sock \
  -v /tmp:/tmp \
  kestra/kestra:latest server local

Once it is running, you can log in to the dashboard at localhost:8080.

For Windows

Refer to the Kestra GitHub repository for detailed instructions: https://github.com/kestra-io/kestra

Sample docker-compose for Kestra:

kestra:
  build: .
  image: kestra/kestra:latest
  container_name: kestra
  user: "0:0"
  environment:
    DOCKER_HOST: tcp://host.docker.internal:2375  # for Windows
    KESTRA_CONFIGURATION: |
      kestra:
        repository:
          type: h2
        queue:
          type: memory
        storage:
          type: local
          local:
            basePath: /app/storage
        tasks:
          tmp-dir:
            path: /app/tmp
        plugins:
          repositories:
            - id: central
              type: maven
              url: [repo.maven.apache.org](https://repo.maven.apache.org/maven2)
          definitions:
            - io.kestra.plugin.core:core:latest
            - io.kestra.plugin.scripts:python:1.3.4
            - io.kestra.plugin.http:http:latest
    KESTRA_TASKS_TMP_DIR_PATH: /app/tmp
  ports:
    - "8080:8080"
  volumes:
    - //var/run/docker.sock:/var/run/docker.sock  # Windows path
    - /yourpath/.dbt:/app/.dbt
    - /yourpath/kestra/plugins:/app/plugins
    - /yourpath/kestra/workflows:/app/workflows
    - /yourpath/kestra/storage:/app/storage
    - /yourpath//kestra/tmp:/app/tmp
    - /yourpath//dbt_prj:/app/workflows/dbt_project
    - /yourpath//my-creds.json:/app/.dbt/my-creds.json
  command: server standalone

Description:

When running the following Docker command in Bash with Docker and WSL2 installed, you may encounter an error. Running Bash as admin will not resolve the issue:

docker run \
  --pull=always \
  --rm \
  -it \
  -p 8080:8080 \
  --user=root \
  -v /var/run/docker.sock:/var/run/docker.sock \
  -v /tmp:/tmp \
  kestra/kestra:latest server local

latest: Pulling from kestra/kestra
Digest: sha256:af02a309ccbb52c23ad1f1551a1a6db8cf0523cf7aac7c7eb878d7925bc85a62
Status: Image is up to date for kestra/kestra:latest
docker: Error response from daemon: mkdir C:\\Program Files\\Git\\var: Access is denied.
See 'docker run --help'.

To resolve this issue, run Command Prompt as an administrator and use the following command:

docker run \
  --pull=always \
  --rm \
  -it \
  -p 8080:8080 \
  --user=root \
  -v "/var/run/docker.sock:/var/run/docker.sock" \
  -v "C:/Temp:/tmp" \
  kestra/kestra:latest server local

After executing the command as described, the localhost should display the Kestra UI as expected.

Error Message

org.postgresql.util.psqlexception the connection attempt failed due to this config on kestra flow -> jdbc:postgresql://host.docker.internal:5432/postgres-zoomcamp

Solution

• Replace host.docker.internal with the name of the service for Postgres in your Docker Compose file.

Additional Error Message

org.postgresql.util.PSQLException: The connection attempt failed. 2025-01-29 22:52:22.281 green_create_table The connection attempt failed. host.docker.internal

Analysis and Solution

• If using Linux, the PostgreSQL database URL differs from the tutorial. Instead of host.docker.internal, Linux users should use the service or container name for Postgres. For example, use:

  jdbc:postgresql://postgres:5432/kestra
  

• Double-check the database name in your Docker Compose file. It might be different from the tutorial; for example, kestra instead of postgres-zoomcamp.

Reminder

• Ensure that the PostgreSQL database name in the Docker Compose matches what you configure in your flow.

I encountered an error where the localhost URL for pgAdmin would just hang (I chose localhost:8080 for my pgAdmin, and made kestra localhost:8090, personal preference).

The associated issue involved permissions. The resolution was to change the ownership of my local directory to the user "5050," which is pgAdmin. Unlike Postgres, pgAdmin requires explicit permission. Apparently, the Postgres user inside the Docker container creates the Postgres volume/dir, so it has permissions already.

This is a useful resource:

Stack Overflow: Permission denied /var/lib/pgadmin/sessions in Docker

Running out of storage while trying to backfill. I realized my GCP VM only has 30GB of storage and I was using it up quickly. Here are a couple of suggestions for managing storage:

• Clean up your GCP VM drive: Use the command below to identify what is taking up the most space:

  sudo du -sh *
  

  • (~1GB) For me, the Anaconda installer was consuming a lot of space. If you no longer need it, you can delete it:

    rm -rf <anacondainstaller_fpath>
    

  • (~3GB) Anaconda itself takes up a lot of space. You can’t delete it entirely if you need Python, but you can clean it up significantly:

    conda clean --all -y
    

• Clean up your Kestra files: Use a purge flow. You can find a generic example here:

  https://kestra.io/docs/administrator-guide/purge

  I wanted to perform the cleanup immediately, rather than waiting until the end of the month, so I adjusted the endDate to "{{ now() }}" and removed the trigger block. You can also choose whether to remove FAILED state executions.

• Clean up your PostgreSQL database: You can manually delete tables in pgAdmin, or set up a workflow in Kestra for it. I found it easy to do manually.

Do not directly add the content of service account credential JSON in Kestra script, especially if you are pushing to GitHub. Follow the instruction to add the service account as a secret Configure Google Service Account.

When you need to use it in Kestra, you can pull it through {{ secret('GCP_SERVICE_ACCOUNT') }} in the pluginDefaults.

When following the YouTube lesson and then running the gcp_setup flow, you might encounter a permission denied error.

To resolve this:

1. Verify if the bucket already exists using the GCP console.
2. If it exists, choose a different name for the bucket.

Note: The GCP bucket name must be unique globally across all buckets, as the bucket will be accessible by URL.

When following the YouTube lesson and then running the gcp_setup flow, the error occurs during the create_bq_dataset task.

The error is less clear, but it stems from using a dash in the dataset name. To resolve this, change the dataset name to something like "de_zoomcamp" to avoid using a dash. This should resolve the error.

Several authentication methods are available; here are some of the most straightforward approaches:

Method 1:

Update your docker-compose.yml file as needed.

Method 2:

1. Store the Service Account as a Secret
   Run this command, specifying the correct path to your service-account.json file and .env_encoded:

   # Example command: Adjust according to your environment
   base64 /path/to/service-account.json > .env_encoded
   

2. Modify docker-compose.yml to Include the Encoded Secrets
   Insert the relevant configuration within your docker-compose.yml.

3. Configure Kestra Plugin Defaults
   This ensures all GCP tasks use the secret automatically.

4. Verify it’s Working in a Testing GCP Workflow

Additional FAQs:

Question: How do I update the Service Account key?

Answer: Generate a new key, re-run the Base64 command, and restart Kestra.

Question: Why use secrets instead of embedding the JSON key in the task?

Answer: Secrets prevent credential exposure and make workflows easier to manage.

Question: Can I apply this method to other GCP tasks?

Answer: Yes, all GCP plugins will automatically inherit the secret.

Yes, you should definitely include the .env_encoded file in your .gitignore file. Here's why:

• Security: The .env_encoded file contains sensitive information, namely the base64 encoded version of your GCP Service Account key. Even though it's encoded, it's not secure to share this in a public repository as anyone can decode it back to the original JSON.

• Best Practices: It's common practice to avoid committing environment files or any files containing secrets to version control systems like Git. This prevents accidental exposure of sensitive data.

How to do it:

• Add this line to your .gitignore:

  .env_encoded
  

More on Security

Base64 encoding is easily reversible. Base64 is an encoding scheme, not an encryption method. It's designed to encode binary data into ASCII characters that can be safely transmitted over systems that are designed to deal with text. Here's why it's not secure for protecting sensitive information:

• Reversibility: Base64 encoding simply translates binary data into a text string using a specific set of 64 characters. Decoding it back to the original data is straightforward and doesn't require any secret key or password.

• Public Availability of Tools: Numerous online tools, software libraries, and command-line utilities exist that can decode base64 with just a few clicks or commands.

• No Security: Since base64 encoding does not change or hide the actual content of the data, anyone with access to the encoded string can decode it back to the original data.

SIGILL in Java Runtime Environment on MacOS M4

Add the following environment variable to your Kestra container: -e JAVA_OPTS="-XX:UseSVE=0":

docker run --rm -it \
  --pull=always \
  -p 8080:8080 \
  --user=root \
  -e JAVA_OPTS="-XX:UseSVE=0" \
  -v /var/run/docker.sock:/var/run/docker.sock \
  -v /tmp:/tmp \
  kestra/kestra:latest server local

The same in a Docker Compose file:

services:
  kestra:
    image: kestra/kestra:latest
    environment:
      JAVA_OPTS: "-XX:UseSVE=0"

If you're using Linux, you might encounter "Connection Refused" errors when connecting to the Postgres DB from within Kestra. This is because host.docker.internal works differently on Linux.

To address this issue:

• Use the modified Docker Compose file mentioned in the "02-workflow-orchestration" README troubleshooting tips.
• Run both Kestra and its dedicated Postgres DB, along with the Postgres DB for exercises, all together using Docker Compose.
• Access the Postgres DB within Kestra by using the container name postgres_zoomcamp instead of host.docker.internal in pluginDefaults.

Make sure to modify the pluginDefaults in the following files:

• 2_postgres_taxi_scheduled.yaml
• 02_postgres_taxi.yaml

This update corrects the Docker Compose configuration to resolve the error when using the alias host.docker.internal on Linux systems. Since this alias does not resolve natively on Linux, the following entry was added to the affected container:

yaml
kestra:
  image: kestra/kestra:latest
  pull_policy: always
  user: "root"
  command: server standalone
  volumes:
    # Add volume configurations here
  environment:
    # Add environment variables here
  ports:
    # Add ports here
  depends_on:
    # Add dependencies here
  extra_hosts:
    - "host.docker.internal:host-gateway"

With this change, containers that need to access host services via host.docker.internal will be able to do so correctly. For inter-container communication within the same network, it is recommended to use the service name directly.

To resolve the issue with host.docker.internal not being recognized on Linux, add the extraHosts configuration to the taskRunner in the dbt-build task:

taskRunner:
  type: io.kestra.plugin.scripts.runner.docker.Docker
  extraHosts:
    - "host.docker.internal:host-gateway"

If you plan on using Kestra with Google Cloud Platform, make sure you set up the GCP_CREDS that will be used in flows with "gcp" in their name.

To set it:

1. Go to Namespaces and select "zoomcamp" if you are using the examples from the lessons.
2. In the KV Store tab, create a new key as GCP_CREDS.
3. Set the type to JSON and paste the content of the .json file with credentials for the service account created.

It seems to be a bug. The current fix is to remove the timezone from triggers in the script. More on this bug is here.

To resolve the issue, you can try the following solutions:

1. Add the -Y flag to apt-get to automatically agree to install additional packages.

   sudo apt-get install -y zip unzip
   

2. Use the Python ZipFile package, which is included in all modern Python distributions. This can bypass the need to install zip and unzip packages.

   from zipfile import ZipFile
   
   with ZipFile('file.zip', 'r') as zip_ref:
       zip_ref.extractall('destination_folder')
   

Make sure to use Nullable data types, such as Int64 when applicable.

Ultimately, when trying to ingest data into a BigQuery table, all files within a given directory must have the same schema.

When dealing with datasets, such as the FHV Datasets from 2019, you may encounter schema inconsistencies. For example, the files for '2019-05' and '2019-06' have the columns "PUlocationID" and "DOlocationID" as integers, while for the period of '2019-01' through '2019-04', the same columns are defined as floats.

When importing these files as Parquet to BigQuery, the first file will define the table schema. All subsequent files must have the same schema to append data correctly.

To prevent errors like this, enforce the data types for the columns on the DataFrame before serializing/uploading them to BigQuery:

pd.read_csv("path_or_url").astype({"col1_name": "datatype", "col2_name": "datatype", ..., "colN_name": "datatype"})

If you receive the error

gzip.BadGzipFile: Not a gzipped file (b'\n\n')

this is because you have specified the wrong URL to the FHV dataset. Make sure to use:

https://github.com/DataTalksClub/nyc-tlc-data/releases/download/fhv/{dataset_file}.csv.gz

Emphasize the /releases/download part of the URL.

• Check the Schema: Ensure that the schema of your dataset is correctly defined.

• Formatting Issues: You might have incorrect formatting in your files.

• Upload Method: Try uploading the CSV.GZ files without formatting or processing them through pandas. Use wget to download if necessary.

Run the following command to check if "BigQuery Command Line Tool" is installed or not:

gcloud components list

You can also use bq.cmd instead of bq to make it work.

Use BigQuery carefully:

• I created my BigQuery dataset on an account where my free trial was exhausted and received a bill of $80.
• Use BigQuery under free credits and destroy all the datasets after creation.
• Check your billing daily, especially if you've spun up a VM.

Be careful when you create your resources on GCP; all of them must share the same region to load data from a GCS Bucket to BigQuery. If you forgot this step, you can create a new dataset in BigQuery using the same region as your GCS Bucket.

This error indicates that your GCS Bucket and the BigQuery dataset are placed in different regions. You need to create a new dataset in BigQuery in the same region as your GCS Bucket and store the data in this newly created dataset.

Make sure to create the BigQuery dataset in the same location as your GCS Bucket. For instance, if your GCS Bucket was created in us-central1, then the BigQuery dataset must also be created in us-central1.

It's important to save your progress in the BigQuery SQL Editor frequently.

Here are some tips:

• Save Regularly: Use the save button at the top bar in the BigQuery SQL Editor. Your saved queries will be available on the left panel.

  

• Alternative Method: Copy and paste your queries into a file using a text editor like Notepad++ or VS Code. Save it with a .sql extension to benefit from syntax highlighting.

By following these methods, you can avoid losing your work in case of unexpected browser issues.

While real-time analytics might not be explicitly mentioned, BigQuery has real-time data streaming capabilities, allowing for potential integration in future project iterations.

could not parse 'pickup_datetime' as timestamp for field pickup_datetime (position 2)

This error is caused by invalid data in the timestamp column. To resolve this issue:

1. Define the schema of the external table using the STRING datatype for the timestamp column. This allows queries to execute without errors.
2. Filter out the invalid timestamp rows during data import.
3. Insert the filtered rows into the materialized table, specifying the TIMESTAMP datatype for the timestamp fields.

When you encounter this BigQuery error, it typically relates to how timestamps are stored in Parquet files.

Solution:

To resolve this issue, you can modify the Parquet writing configuration by adding use_deprecated_int96_timestamps=True to the pq.write_to_dataset function. This setting writes timestamps in the INT96 format, which can be more compatible with BigQuery.

Here’s how you can adjust the function:

pq.write_to_dataset(
    table,
    root_path=root_path,
    filesystem=gcs,
    use_deprecated_int96_timestamps=True  # Write timestamps to INT96 Parquet format
)

References

• Stack Overflow - Parquet compatibility with PyArrow vs PySpark
• Stack Overflow - Editing Parquet files and datetime format errors
• Reddit - Parquet Timestamp to BQ issues

Use the above configuration to ensure compatibility with Google BigQuery when dealing with timestamps in Parquet files.

Solution:

If you’re using Mage, in the last Data Exporter that writes to Google Cloud Storage, use PyArrow to generate the Parquet file with the correct logical type for the datetime columns. Otherwise, they won't be converted to a timestamp when loaded by BigQuery later on.

import pyarrow as pa
import pyarrow.parquet as pq
import os

if 'data_exporter' not in globals():
    from mage_ai.data_preparation.decorators import data_exporter

# Replace with the location of your service account key JSON file.
os.environ['GOOGLE_APPLICATION_CREDENTIALS'] = '/home/src/personal-gcp.json'

bucket_name = "<YOUR_BUCKET_NAME>"
object_key = 'nyc_taxi_data_2022.parquet'
where = f'{bucket_name}/{object_key}'

@data_exporter
def export_data(data, *args, **kwargs):
    table = pa.Table.from_pandas(data, preserve_index=False)
    gcs = pa.fs.GcsFileSystem()
    pq.write_table(
        table,
        where,
        # Convert integer columns in Epoch milliseconds
        # to Timestamp columns in microseconds ('us') so
        # they can be loaded into BigQuery with the right
        # data type
        coerce_timestamps='us',
        filesystem=gcs
    )

Solution 2:

If you’re using Mage, in the last Data Exporter that writes to Google Cloud Storage, provide PyArrow with an explicit schema to generate the Parquet file with the correct logical type for the datetime columns.

schema = pa.schema([
    ('vendor_id', pa.int64()),
    ('lpep_pickup_datetime', pa.timestamp('ns')),
    ('lpep_dropoff_datetime', pa.timestamp('ns')),
    ('store_and_fwd_flag', pa.string()),
    ('ratecode_id', pa.int64()),
    ('pu_location_id', pa.int64()),
    ('do_location_id', pa.int64()),
    ('passenger_count', pa.int64()),
    ('trip_distance', pa.float64()),
    ('fare_amount', pa.float64()),
    ('extra', pa.float64()),
    ('mta_tax', pa.float64()),
    ('tip_amount', pa.float64()),
    ('tolls_amount', pa.float64()),
    ('improvement_surcharge', pa.float64()),
    ('total_amount', pa.float64()),
    ('payment_type', pa.int64()),
    ('trip_type', pa.int64()),
    ('congestion_surcharge', pa.float64()),
    ('lpep_pickup_month', pa.int64())
])

table = pa.Table.from_pandas(data, schema=schema)