Hi There,

thanks for this package, very helpful. It's unclear to me exactly what is being output by this tool? Is it a sum of download counts from various sources? How does that differ from "pip only" option -p? Is there a way to get unique download count etc? Some more details would be helpful.

The default output for my packages is almost 6-10 times higher than if use the --pip option - any idea on why is that? I only ever recommended people to use my package via pip install. Although there are some dev, who clone and install locally outside pip, that number is likely very small. So the --pip option should closely match the default count (unless there is lot more going on which I don't understand).

Also, if I would like to estimate "usage" (which is a higher bar from download) from this, would it make sense?

Thanks for your help.

First of all, thank you so much for building this package. I'm excited to start using it, but it has been crashing for me on every query run:

coding@Aarons-MBP pypinfo (master)$ pypinfo django date
Traceback (most recent call last):
  File "/usr/local/bin/pypinfo", line 11, in <module>
    load_entry_point('pypinfo==5.0.0', 'console_scripts', 'pypinfo')()
  File "/usr/local/lib/python3.6/site-packages/click/core.py", line 722, in __call__
    return self.main(*args, **kwargs)
  File "/usr/local/lib/python3.6/site-packages/click/core.py", line 697, in main
    rv = self.invoke(ctx)
  File "/usr/local/lib/python3.6/site-packages/click/core.py", line 1043, in invoke
    return Command.invoke(self, ctx)
  File "/usr/local/lib/python3.6/site-packages/click/core.py", line 895, in invoke
    return ctx.invoke(self.callback, **ctx.params)
  File "/usr/local/lib/python3.6/site-packages/click/core.py", line 535, in invoke
    return callback(*args, **kwargs)
  File "/usr/local/lib/python3.6/site-packages/click/decorators.py", line 17, in new_func
    return f(get_current_context(), *args, **kwargs)
  File "/usr/local/lib/python3.6/site-packages/pypinfo/cli.py", line 89, in pypinfo
    query = client.run_sync_query(built_query)
AttributeError: 'Client' object has no attribute 'run_sync_query'

I tried python2, updating gcloud, etc. to no avail.

Hi,

I tried your project and thik it's really cool. I maintain a small project with few download. When I queried the dataset for the last year it said 20k downloads. This most likely includes spiders and PyPI mirrors. For this to be useful for smaller projects it would be great to try and filter these things out by default.

I for instance have downloads from Python version 1.17, which does not seem right. The majority of my downloads come from the system None (which I assume happens when downloaded over HTTP by clicking the link on the website. Maybe a baseline could be computed based on the minimum number of downloads for any project during the same time interval?

Hi Everyone, I am getting this error despite being on the latest version:

Installing collected packages: pypinfo
Successfully installed pypinfo-19.0.0

WARNING: You are using pip version 21.1.2; however, version 21.2.4 is available.
You should consider upgrading via the '/Users/Reddy/anaconda3/envs/py36/bin/python -m pip install --upgrade pip' command.

(base) $ 16:37:33 Quark doc >>  pypinfo confounds
Traceback (most recent call last):
  File "/Users/Reddy/anaconda3/bin/pypinfo", line 11, in <module>
    sys.exit(pypinfo())
  File "/Users/Reddy/anaconda3/lib/python3.6/site-packages/click/core.py", line 764, in __call__
    return self.main(*args, **kwargs)
  File "/Users/Reddy/anaconda3/lib/python3.6/site-packages/click/core.py", line 717, in main
    rv = self.invoke(ctx)
  File "/Users/Reddy/anaconda3/lib/python3.6/site-packages/click/core.py", line 1114, in invoke
    return Command.invoke(self, ctx)
  File "/Users/Reddy/anaconda3/lib/python3.6/site-packages/click/core.py", line 956, in invoke
    return ctx.invoke(self.callback, **ctx.params)
  File "/Users/Reddy/anaconda3/lib/python3.6/site-packages/click/core.py", line 555, in invoke
    return callback(*args, **kwargs)
  File "/Users/Reddy/anaconda3/lib/python3.6/site-packages/click/decorators.py", line 17, in new_func
    return f(get_current_context(), *args, **kwargs)
  File "/Users/Reddy/anaconda3/lib/python3.6/site-packages/pypinfo/cli.py", line 150, in pypinfo
    query_rows = query_job.result(timeout=timeout // 1000)
  File "/Users/Reddy/anaconda3/lib/python3.6/site-packages/google/cloud/bigquery/job.py", line 2762, in result
    super(QueryJob, self).result(timeout=timeout)
  File "/Users/Reddy/anaconda3/lib/python3.6/site-packages/google/cloud/bigquery/job.py", line 703, in result
    return super(_AsyncJob, self).result(timeout=timeout)
  File "/Users/Reddy/anaconda3/lib/python3.6/site-packages/google/api_core/future/polling.py", line 127, in result
    raise self._exception
google.api_core.exceptions.BadRequest: 400 FROM clause with table wildcards matches no table
(base) $ 16:37:43 Quark doc >>

appreciate your help or fix!

Originally posted by @raamana in https://github.com/ofek/pypinfo/issues/114#issuecomment-905857544

Hi there. The installation instructions in your README includes the following link in step 5:

https://cloud.google.com/storage/docs/authentication#generating-a-private-key

This target does not appear to exist. As such, I am unsure how to "create credentials in JSON format."

Thanks, Tom

This is relatively painless, I swear.

Well...

During creation, choose BigQuery User as role.

First off, which of

• API key
• OAuth client ID
• Service accout key

do I choose? I suppose Service account key since this is the only one that takes me to another screen with role in it. There's no such rule as BigQuery User however.

Hi,

After installation following instructions given in the README.md, I tried a simplest call:

pypinfo requests

Unfortunately getting following exception:

...
  File "/Users/christianr/.venv/sett/lib/python3.9/site-packages/pypinfo/cli.py", line 166, in pypinfo
    query_rows = query_job.result(timeout=timeout // 1000)
  File "/Users/christianr/.venv/sett/lib/python3.9/site-packages/google/cloud/bigquery/job/query.py", line 1160, in result
    super(QueryJob, self).result(retry=retry, timeout=timeout)
  File "/Users/christianr/.venv/sett/lib/python3.9/site-packages/google/cloud/bigquery/job/base.py", line 631, in result
    return super(_AsyncJob, self).result(timeout=timeout, **kwargs)
  File "/Users/christianr/.venv/sett/lib/python3.9/site-packages/google/api_core/future/polling.py", line 134, in result
    raise self._exception
google.api_core.exceptions.BadRequest: 400 No matching signature for function DATE_ADD for argument types: TIMESTAMP, INTERVAL INT64 DATE_TIME_PART. Supported signature: DATE_ADD(DATE, INTERVAL INT64 DATE_TIME_PART) at [5:25]

(job ID: 947e6084-e5e6-4bb5-ae12-bb8faad8ec0b)

                                                 -----Query Job SQL Follows-----

    |    .    |    .    |    .    |    .    |    .    |    .    |    .    |    .    |    .    |    .    |    .    |    .    |
   1:SELECT
   2:  FORMAT_TIMESTAMP("%Y", timestamp) as download_year,
   3:  COUNT(*) as download_count,
   4:FROM `the-psf.pypi.file_downloads`
   5:WHERE timestamp BETWEEN DATE_ADD(CURRENT_TIMESTAMP(), INTERVAL -1826 DAY) AND DATE_ADD(CURRENT_TIMESTAMP(), INTERVAL -1 DAY)
   6:  AND file.project = "blist"
   7:  AND details.installer.name = "pip"
   8:GROUP BY
   9:  download_year
  10:ORDER BY
  11:  download_count DESC
  12:LIMIT 10
    |    .    |    .    |    .    |    .    |    .    |    .    |    .    |    .    |    .    |    .    |    .    |    .    |

Using the SQL query in the BigQuery SQL workspace pops up the same error message if I use the public PyPI download statistics dataset.

My environment:

pypinfo, version 18.0.0
Python 3.9.1

By using the clustered data table, performance if improved because BigQuery can skip of data that doesn't match the desired project.

Tested locally:

$ pypinfo google-cloud-bigquery        Served from cache: False
Data processed: 740.43 MiB
Data billed: 741.00 MiB
Estimated cost: $0.01

| download_count |
| -------------- |
|     10,149,146 |

Tested locally with all supported fields:

$ pypinfo google-cloud-bigquery project version file pyversion percent3 percent2 impl impl-version openssl date month year country installer installer-version setuptools-version system system-release distro distro-version cpu libc libc-version
Served from cache: False
Data processed: 4.62 GiB
Data billed: 4.62 GiB
Estimated cost: $0.03

| project               | version | file                                              | python_version | percent_3 | percent_2 | implementation | impl_version | openssl_version | download_date | download_month | download_year | country | installer_name | installer_version | setuptools_version | system_name | system_release    | distro_name      | distro_version | cpu    | libc_name | libc_version | download_count |
| --------------------- | ------- | ------------------------------------------------- | -------------- | --------- | --------- | -------------- | ------------ | --------------- | ------------- | -------------- | ------------- | ------- | -------------- | ----------------- | ------------------ | ----------- | ----------------- | ---------------- | -------------- | ------ | --------- | ------------ | -------------- |
| google-cloud-bigquery | 1.24.0  | google_cloud_bigquery-1.24.0-py2.py3-none-any.whl | 3.7            | 100.0     | 0.0       | CPython        | 3.7          | 1.1.0l          | 2020-12-15    | 2020-12        |         2,020 | US      | pip            | 20.0.2            | 45.1.0             | Linux       | 5.4.49+           | Debian GNU/Linux |              9 | x86_64 | glibc     | 2.24         |        171,636 |
| google-cloud-bigquery | 1.24.0  | google_cloud_bigquery-1.24.0-py2.py3-none-any.whl | 3.7            | 100.0     | 0.0       | CPython        | 3.7          | 1.1.0l          | 2020-12-13    | 2020-12        |         2,020 | US      | pip            | 20.0.2            | 45.1.0             | Linux       | 5.4.49+           | Debian GNU/Linux |              9 | x86_64 | glibc     | 2.24         |        160,152 |
| google-cloud-bigquery | 2.6.1   | google_cloud_bigquery-2.6.1-py2.py3-none-any.whl  | 3.7            | 100.0     | 0.0       | CPython        | 3.7          | 1.1.1           | 2021-01-07    | 2021-01        |         2,021 | IN      | pip            | 20.0.2            | 45.2.0             | Linux       | 4.15.0-1092-azure | Ubuntu           |          18.04 | x86_64 | glibc     | 2.27         |        136,529 |
| google-cloud-bigquery | 2.6.1   | google_cloud_bigquery-2.6.1-py2.py3-none-any.whl  | 3.7            | 100.0     | 0.0       | CPython        | 3.7          | 1.1.1           | 2021-01-06    | 2021-01        |         2,021 | IN      | pip            | 20.0.2            | 45.2.0             | Linux       | 4.15.0-1092-azure | Ubuntu           |          18.04 | x86_64 | glibc     | 2.27         |        133,927 |
| google-cloud-bigquery | 1.24.0  | google_cloud_bigquery-1.24.0-py2.py3-none-any.whl | 3.7            | 100.0     | 0.0       | CPython        | 3.7          | 1.1.0l          | 2020-12-14    | 2020-12        |         2,020 | US      | pip            | 20.0.2            | 45.1.0             | Linux       | 5.4.49+           | Debian GNU/Linux |              9 | x86_64 | glibc     | 2.24         |        130,400 |
| google-cloud-bigquery | 2.6.1   | google_cloud_bigquery-2.6.1-py2.py3-none-any.whl  | 3.7            | 100.0     | 0.0       | CPython        | 3.7          | 1.1.1           | 2021-01-05    | 2021-01        |         2,021 | IN      | pip            | 20.0.2            | 45.2.0             | Linux       | 4.15.0-1092-azure | Ubuntu           |          18.04 | x86_64 | glibc     | 2.27         |        129,844 |
| google-cloud-bigquery | 2.6.1   | google_cloud_bigquery-2.6.1-py2.py3-none-any.whl  | 3.7            | 100.0     | 0.0       | CPython        | 3.7          | 1.1.1           | 2021-01-09    | 2021-01        |         2,021 | IN      | pip            | 20.0.2            | 45.2.0             | Linux       | 4.15.0-1050-azure | Ubuntu           |          18.04 | x86_64 | glibc     | 2.27         |        126,544 |
| google-cloud-bigquery | 2.6.1   | google_cloud_bigquery-2.6.1-py2.py3-none-any.whl  | 3.7            | 100.0     | 0.0       | CPython        | 3.7          | 1.1.1           | 2021-01-04    | 2021-01        |         2,021 | IN      | pip            | 20.0.2            | 45.2.0             | Linux       | 4.15.0-1092-azure | Ubuntu           |          18.04 | x86_64 | glibc     | 2.27         |        124,986 |
| google-cloud-bigquery | 2.6.1   | google_cloud_bigquery-2.6.1-py2.py3-none-any.whl  | 3.7            | 100.0     | 0.0       | CPython        | 3.7          | 1.1.1           | 2020-12-30    | 2020-12        |         2,020 | IN      | pip            | 20.0.2            | 45.2.0             | Linux       | 4.15.0-1092-azure | Ubuntu           |          18.04 | x86_64 | glibc     | 2.27         |        121,202 |
| google-cloud-bigquery | 2.6.1   | google_cloud_bigquery-2.6.1-py2.py3-none-any.whl  | 3.7            | 100.0     | 0.0       | CPython        | 3.7          | 1.1.1           | 2021-01-10    | 2021-01        |         2,021 | IN      | pip            | 20.0.2            | 45.2.0             | Linux       | 4.15.0-1050-azure | Ubuntu           |          18.04 | x86_64 | glibc     | 2.27         |        121,104 |
| Total                 |         |                                                   |                |           |           |                |              |                 |               |                |               |         |                |                   |                    |             |                   |                  |                |        |           |              |      1,356,324 |

Closes #64

When i run pypinfo --auth key.json with the BigQuery key i have the given answer :

$ pypinfo --auth key.json
Traceback (most recent call last):
  File "~/.local/bin/pypinfo", line 8, in <module>
    sys.exit(pypinfo())
  File "~/.local/lib/python3.8/site-packages/click/core.py", line 829, in __call__
    return self.main(*args, **kwargs)
  File "~/.local/lib/python3.8/site-packages/click/core.py", line 782, in main
    rv = self.invoke(ctx)
  File "~/.local/lib/python3.8/site-packages/click/core.py", line 1236, in invoke
    return Command.invoke(self, ctx)
  File "~/.local/lib/python3.8/site-packages/click/core.py", line 1066, in invoke
    return ctx.invoke(self.callback, **ctx.params)
  File "~/.local/lib/python3.8/site-packages/click/core.py", line 610, in invoke
    return callback(*args, **kwargs)
  File "~/.local/lib/python3.8/site-packages/click/decorators.py", line 21, in new_func
    return f(get_current_context(), *args, **kwargs)
  File "~/.local/lib/python3.8/site-packages/pypinfo/cli.py", line 119, in pypinfo
    set_credentials(auth)
  File "~/.local/lib/python3.8/site-packages/pypinfo/db.py", line 25, in set_credentials
    tr.insert({'path': creds_file})
  File "~/.local/lib/python3.8/site-packages/tinyrecord/transaction.py", line 86, in __exit__
    self.record.execute()
  File "~/.local/lib/python3.8/site-packages/tinyrecord/changeset.py", line 21, in execute
    data = self.db._read()
AttributeError: 'Table' object has no attribute '_read'

There is probably something about tinydb package

Simply adding a colon will align columns nicely in Markdown.

See https://help.github.com/articles/organizing-information-with-tables/#formatting-content-within-your-table

Builds on #23 by including a unit test for this change (eg.). This makes things much easier to develop without going over your quota!

Before

As plaintext:

| python_version | percent | download_count |
| -------------- | ------- | -------------- |
| 2.7            |   59.3% |          3,048 |
| 3.5            |   20.9% |          1,074 |
| 3.6            |   14.3% |            734 |
| 3.4            |    5.4% |            279 |
| 2.6            |    0.1% |              4 |

As rendered Markdown:

| python_version | percent | download_count | | -------------- | ------- | -------------- | | 2.7 | 59.3% | 3,048 | | 3.5 | 20.9% | 1,074 | | 3.6 | 14.3% | 734 | | 3.4 | 5.4% | 279 | | 2.6 | 0.1% | 4 |

After

As plaintext:

| python_version | percent | download_count |
| -------------- | ------: | -------------: |
| 2.7            |   59.3% |          3,048 |
| 3.5            |   20.9% |          1,074 |
| 3.6            |   14.3% |            734 |
| 3.4            |    5.4% |            279 |
| 2.6            |    0.1% |              4 |

As rendered Markdown:

| python_version | percent | download_count | | -------------- | ------: | -------------: | | 2.7 | 59.3% | 3,048 | | 3.5 | 20.9% | 1,074 | | 3.6 | 14.3% | 734 | | 3.4 | 5.4% | 279 | | 2.6 | 0.1% | 4 |

After spending a bunch of time setting up the GCP account, and looking at usage, it seems to be very expensive.

After the free tier is consumed and the 90day $300 credit expires, BigQuery costs $5 per TB of data queried. Querying the simple_requests table which is 49.77 TB would cost $250 per query.

A query of the distribution_metadata table (18.8GB) would cost $0.094 per query. Again very expensive..

Placing the data in AWS S3 using S3 Select cost about the same, still very expensive.

I think this should be fully disclosed in the readme file

I would like to use the data to correlate with openSUSE package names, which use the 'real' name supplied in setup.py, i.e. not-normalised.

I've been doing a bit of research at hugovk/top-pypi-packages#4 and https://github.com/psincraian/pepy/issues/128, and the raw data from bigquery can include this, with a very small perf hit.

The query only needs to change from selecting file.project to substr(max(file.filename),1,LENGTH(file.project)) , or more likely including both.

Note this does depend on using standard SQL ( https://github.com/ofek/pypinfo/issues/28 ).

Do we know the cost implications of those changes?

Hello,

It would be great to be able to tell project tendencies. E.g are my monthly usage going up or down? This is already possible with playing around the start and end date, but what about providing a builtin way of this, that does not require shell scripting it?

Whilst making https://github.com/ofek/pypinfo/pull/29 I got this:

    raise exceptions.from_http_response(response)
google.api_core.exceptions.Forbidden: 403 GET https://www.googleapis.com/bigquery/v2/projects/pypinfo-hugovk/queries/<snip>?maxResults=0&timeoutMs=10000: Quota exceeded: Your project exceeded quota for free query bytes scanned. For more information, see https://cloud.google.com/bigquery/troubleshooting-errors

I can't find how much of my free monthly TB I've used up on the Google console.

After some digging in the console I found this:

https://console.cloud.google.com/home/activity?project=pypinfo-hugovk&authuser=1

Linking to this:

https://console.cloud.google.com/iam-admin/quotas?project=pypinfo-hugovk&authuser=1

Which isn't very informative -- all zeroes and dashes!

I get the error after doing several pypinfo --percent --pip pypinfo pyversion, but pypinfo --percent --pip -d 1 pypinfo is still fine.

Any idea what "QUERY-MBYTES-FOR-UNBILLED-PROJECTS-per-project" really means?

And where to check the monthly 1TB quota?

Thanks!