steampipe plugin install twitter

Table: twitter_search_recent - Query Twitter Recent Searches using SQL

Twitter is a social media platform that allows users to post and interact with messages known as "tweets". Twitter API provides programmatic access to Twitter data, including user profile information, tweets, and more. The recent searches resource provides information about the most recent search queries made by a user.

Table Usage Guide

The twitter_search_recent table provides insights into the most recent search queries made on Twitter. As a data analyst, you can explore query-specific details through this table, including the query text, the timestamp of the query, and other related metadata. Use it to understand user behavior, track trending topics, or analyze the popularity of certain keywords or hashtags.

Important Notes

  • Search the last 7 days of public tweets using the Twitter search syntax and operators.
  • The query field must be set in the where clause.
  • To prevent excess API quota use, results are limited to max_results_per_query by default.

Examples

Tweets matching a hashtag

Identify instances where specific hashtags are used on Twitter. This can help in tracking the popularity and reach of a marketing campaign or trending topic.

select
*
from
twitter_search_recent
where
query = '#rowscoloredglasses';
select
*
from
twitter_search_recent
where
query = '#rowscoloredglasses';

Tweets mentioning a user

Discover the segments that mention a specific user on Twitter. This can be useful to understand the public sentiment or perception about the user in the Twitter community.

select
*
from
twitter_search_recent
where
query = '@steampipeio';
select
*
from
twitter_search_recent
where
query = '@steampipeio';

Tweets by a given author

Explore the recent posts made by a specific Twitter user. This can be useful for understanding the content and frequency of their tweets, which could be beneficial for social media analysis or competitor research.

select
*
from
twitter_search_recent
where
query = 'from:steampipeio';
select
*
from
twitter_search_recent
where
query = 'from:steampipeio';

Tweets in reply to a user

Explore which tweets are in response to a specific user to understand their social engagement and interactions on Twitter. This can be useful in identifying trends, gauging public sentiment, or tracking customer service interactions.

select
*
from
twitter_search_recent
where
query = 'to:steampipeio';
select
*
from
twitter_search_recent
where
query = 'to:steampipeio';

Explore the tweets that contain a specific URL to gain insights into the public discussion related to a particular website, in this case, 'steampipe.io'. This could be useful for monitoring brand mentions, tracking campaign performance, or understanding audience engagement.

select
*
from
twitter_search_recent
where
query = 'url:steampipe.io';
select
*
from
twitter_search_recent
where
query = 'url:steampipe.io';

Place, author, and text for tweets about the weather in Vermont

select
place ->> 'full_name' as place,
author ->> 'username' as author,
text
from
twitter_search_recent
where
query = 'weather'
and place ->> 'full_name' ~* ' vt$' -- regex matches 'Barre VT' etc;

Tweets about weather within 10 miles of a lat/lon location

Note: point_radius and related operators are not available with a basic ("Essential") account, see operators by product.

select
*
from
twitter_search_recent
where
query = 'weather point_radius:[-105.292778 40.019444 10mi]' ;

Schema for twitter_search_recent

NameTypeOperatorsDescription
_ctxjsonbSteampipe context in JSON form, e.g. connection_name.
attachment_pollsjsonbPolls attached to the Tweet.
attachmentsjsonbSpecifies the type of attachments (if any) present in this Tweet.
authorjsonbAuthor of the Tweet.
author_idtextUnique identifier of the author of the Tweet.
cashtagsjsonbList of cashtags (e.g. $TWTR) mentioned in the Tweet.
context_annotationsjsonbContains context annotations for the Tweet.
conversation_idtextThe Tweet ID of the original Tweet of the conversation (which includes direct replies, replies of replies).
created_attimestamp with time zoneCreation time of the Tweet.
entitiesjsonbContains details about text that has a special meaning in a Tweet.
geojsonbContains details about the location tagged by the user in this Tweet, if they specified one.
hashtagsjsonbList of hashtags (e.g. #sql) mentioned in the Tweet.
idtextUnique identifier of this Tweet.
in_reply_to_user_idtextIf this Tweet is a Reply, indicates the user ID of the parent Tweet's author.
in_reply_userjsonbUser the Tweet was in reply to.
langtextLanguage of the Tweet, if detected by Twitter. Returned as a BCP47 language tag.
mentionsjsonbList of users (e.g. steampipeio) mentioned in the Tweet.
mentions_objjsonbUsers mentioned in the Tweet.
placejsonbPlace where the Tweet was created.
possibly_sensitivebooleanIndicates if this Tweet contains URLs marked as sensitive, for example content suitable for mature audiences.
public_metricsjsonbEngagement metrics for the Tweet at the time of the request.
querytext=Query string for the exploit search.
quotedtextIf this Tweet is a Quote Tweet, indicates the ID of the original Tweet.
referenced_tweetsjsonbTweets referenced in this Tweet.
replied_totextIf this Tweet is a Reply, indicates the ID of the Tweet it is a reply to.
retweetedtextIf this Tweet is a Retweet, indicates the ID of the orginal Tweet.
sourcetextThe name of the app the user Tweeted from.
texttextThe content of the Tweet.
urlsjsonbList of URLs (e.g. https://steampipe.io) mentioned in the Tweet.
withheldjsonbContains withholding details for withheld content.

Export

This table is available as a standalone Exporter CLI. Steampipe exporters are stand-alone binaries that allow you to extract data using Steampipe plugins without a database.

You can download the tarball for your platform from the Releases page, but it is simplest to install them with the steampipe_export_installer.sh script:

/bin/sh -c "$(curl -fsSL https://steampipe.io/install/export.sh)" -- twitter

You can pass the configuration to the command with the --config argument:

steampipe_export_twitter --config '<your_config>' twitter_search_recent