search

TrainingByPackt / SQL-for-Data-Analytics

Perform fast and efficient data analysis with the power of SQL
MIT License
263 stars 432 forks source link

Loading the Sample Databases #4

Open jvl98l opened 3 years ago

jvl98l commented 3 years ago

In the book, it states "To install the database on PostgreSQL, copy the data.dump file from the Datasets folder in the GitHub repository of the book.......Then, load the data.dump file from a command line using the command: psql < data.dump"

Can someone please provide clarification on how this is done?? I've had the same issue as someone else when clicking on file that it brings up the code in a separate window. The book did not do a good job at explaining much of anything in terms of downloading software or loading the data.

bmag618 commented 3 years ago

Had the same issue on Mac The issue I encountered was due to a later version of OpenSSL I had: v1.1.1.dylib I needed to point to an earlier version: v1.0.0.dylib When I executed the psql command psql I got the following error:

"dyld: Library not loaded: /usr/local/opt/openssl/lib/libssl.1.0.0.dylib Referenced from: /usr/local/lib/libpq.5.dylib Reason: image not found Abort trap: 6"

The resolution I used:

1) brew uninstall --ignore-dependencies openssl brew install https://github.com/tebelorg/Tump/releases/download/v1.0.0/openssl.rb

2) brew switch openssl ls /usr/local/cellar/openssl | sed 's/\///g'

3) psql -U user -d database < /Users/user/directory/data.dump

Hope this helps

Jpfeiff42 commented 3 years ago

Obviously, it's probably a little late to help out OP, but hopefully this helps other students who are stumped by this as well.

Here is the way I figured it out:

  1. Open the SQL Shell (psql), that came with the PostgreSQL installation package.
  2. Connect to the server/database/user that you would like to use. If you are just using the default settings, you can just press enter until it prompts you for the password.
  3. Type in the password you set for that user and press Enter
  4. On the command line, change your directory to the location of the data.dump file. Using this command: dbname=#\cd C:/Users/..../SQL-for-Data-Analytics-master/Datasets (Make sure you pay attention to the direction of the slash marks. If you copy and paste the location from the File Properties in Windows, the slashes will be wrong)
  5. Now that you are in the correct directory, you can use a psql command to load the dump file: dbname=# \i data.dump
  6. After psql has finished loading the file, I had to exit and restart the psql terminal before my database would recognize the new tables. I don't know if that is 100% necessary, or if a COMMIT statement would do the same thing.

Best of luck!

jco1131 commented 2 months ago

Mine asks for a username, not a password, and then ignores all commands and exits after I press any key. Maybe my brain is too fried but I cannot figure out what to do to upload the databases. I've done the command line, I've tried simply creating the database within but run into the data error when it comes inserting the data into the tables for smalljoins, I've tried simply doing it step by step. I'm totally lost and my instructor is busy so slow to reply unfortunately. I've been stuck with this for the last week or so racking my brain and just feeling defeated and frustrated at this point. I'm sure I know what to do but cannot figure it out lol