Data preparation and database

[JoernBFH]

Hi

I was trying to run the "dbt run" commands locally after having (also locally) acquired the data. But it always fails.

From a fresh transfermarkt-datasets installation I got (full log file attached)

_Completed with 2 errors and 0 warnings:
Runtime Error in model base_games (models\base\transfermarkt_scraper\base_games.sql)
Invalid Input Error: The delimiter option cannot exceed a size of 1 byte.

Runtime Error in model base_players (models\base\transfermarkt_scraper\base_players.sql)
Invalid Input Error: The delimiter option cannot exceed a size of 1 byte.

Done. PASS=0 WARN=0 ERROR=2 SKIP=2 TOTAL=4_

I though this may be due to the command "columns=struct_pack(value := 'VARCHAR'), delim='\1', quote='\0'," in sources.yml so I changes it to "columns=struct_pack(value := 'VARCHAR'), delim=',', quote='"',". But this also gave errors:

Runtime Error in model base_games (models\base\transfermarkt_scraper\base_games.sql)
Invalid Input Error: Error when sniffing file "..\data\raw\transfermarkt-scraper\2012\games.json.gz".
CSV options could not be auto-detected. Consider setting parser options manually.

Since I don't know sql and duckdb I'm out of my depth here. Am I just doing something basic wrong?

Your help is much appreciated.

Cheers
Joern
dbt.log

[dcaribou]

How do they look the files you acquired locally? Do they contain data in JSON lines as expected?
I'd start by having a look at those files to confirm that the structure is as it should.

I get that you run the acquiring locally and it worked, but it might have produced empty or invalid files somehow.

[JoernBFH]

Thank you very much for your quick answer!
With your tip I deleted my locally acquired data and downloaded it via "dvc pull". But the errors remained the same. Also, from what I can tell, the JSON lines look all right.

I already wonder about the delimiter issue. Does the delim='\1' work for you? And if so, why does it throw me that Invalid Input Error?

Thanks again so much!

[dcaribou]

On which system are you on? (Windows, Linux, Mac)

I cannot reproduce your error. I've tested it on a Codespace and the dbt run command worked fine. Have you tried in a Codespace?

[JoernBFH]

I'm running it on Windows 10.

I just tried it (never used codespace before). After installing dbt, copying my dbt_cloud.yml and issuing "dbt deps" I got an error;

The specified project was not found or development credentials haven't been set on the project. Ensure that you have the correct permissions to access the project and development credentials are set in dbt Cloud: https://cloud.getdbt.com/settings/profile/credentials"

[dcaribou]

You got that error in the Codespace? How did you create the Codespace?

Why are you using a dbt_cloud.yml? This project uses only dbt Core, which runs in your local, not dbt Cloud.
You should not need to add any file of your own, just installing, activating the environment, pulling the data and running it.

[JoernBFH]

I setup a new Codespace and now it's working! Now I just have to get this working locally on my PC. But I guess somehow my installation is messed up. So, I'll start from scratch.

BTW (and it's probably also due to my system somehow), I cannot clone your repository anymore:
Cloning into 'C:\Users\Jörn\40_Projekte\transfermarkt-datasets'...
remote: Enumerating objects: 3784, done.
remote: Counting objects: 100% (1721/1721), done.
remote: Compressing objects: 100% (866/866), done.
remote: Total 3784 (delta 1002), reused 1481 (delta 845), pack-reused 2063
Receiving objects: 100% (3784/3784), 3.83 MiB | 20.34 MiB/s, done.
Resolving deltas: 100% (1975/1975), done.
error: invalid path 'streamlit/pages/02_🔎_app:_explore_dataset.py'
fatal: unable to checkout working tree
warning: Clone succeeded, but checkout failed.
You can inspect what was checked out with 'git status'
and retry with 'git restore --source=HEAD :/'

Thank you very much again. You've been really very helpful!!!

Cheers,
Joern

[dcaribou]

I'm glad it's working now 😄 .
The cloning error is a known issue for Windows.

You can see other FAQs here.

[JoernBFH]

Do you use Linux locally? I'm still struggling here with the "Invalid Input Error: The delimiter option cannot exceed a size of 1 byte." Really strange as it's working on codespace. Could it be an encoding issue?
Has anyone successfully used the "dbt run" command locally on a Windows system?

[dcaribou]

Yes, it could be. You could try with a different delimiter.
The delimiter I used (\1) is a dummy one, it actually does not matter which delimiter character is used as long as it is a character that won't be found in the JSON line. So you could try with any other character, as long as it meets that criteria.

transfermarkt-datasets/dbt/models/base/transfermarkt_scraper/sources.yml

Line 12 in ea2cd06

columns=struct_pack(value := 'VARCHAR'), delim='\1', quote='\0',

I am on Mac. I don't know if someone has run it in Windows successfully.